Is the XLOOKUP generator free?

Yes, completely free. 10 AI formula queries per day, no signup or credit card required.

What does the if_not_found argument do in XLOOKUP?

The if_not_found argument is the 4th parameter and replaces the need for an IFERROR wrapper. Example: =XLOOKUP(A2, B:B, C:C, Not found) returns your custom text instead of #N/A when no match exists, making formulas cleaner than VLOOKUP with IFERROR.

How do I use XLOOKUP to return a column to the left?

XLOOKUP has no direction restriction. =XLOOKUP(D2, C:C, A:A, Not found) returns column A even though it is to the left of the lookup column C. VLOOKUP requires the return column to be to the right of the lookup column.

How do I use XLOOKUP with two conditions?

Combine lookup values and arrays with ampersand: =XLOOKUP(E2&F2, A:A&B:B, C:C, Not found). This matches rows where column A equals E2 AND column B equals F2, then returns column C. No helper column is needed.

Is XLOOKUP available in all Excel versions?

XLOOKUP is available in Excel 365, Excel 2021, and Google Sheets from 2022. It is NOT available in Excel 2019 or older and will show a #NAME? error in those versions. Use VLOOKUP or INDEX MATCH for files that need to open in older Excel.

XLOOKUP Formula Generator — Free AI Tool

What is XLOOKUP Formula Generator?

What is XLOOKUP?

XLOOKUP is the modern replacement for VLOOKUP, available in Excel 365, Excel 2021, and Google Sheets (2022+). Unlike VLOOKUP, XLOOKUP can search in any direction, handles 'not found' cases natively, and won't break when you insert columns. It's the recommended lookup function for any spreadsheet built today.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

How to Use This Generator

Go to the ExcelForm tool on the homepage
Click the Generate tab (or the relevant tool tab for your task)
Describe what you want in plain English — be specific about column names, sheet names, and conditions
Select your environment (Excel, Google Sheets, or Both)
Click Generate Formula and copy the result directly into your spreadsheet

Example: XLOOKUP Formula Generator in Action

📝 What you type

"Find the price in column C of Sheet2 where the product name in column A matches my search in cell A2. Show 'Not found' if missing."

✅ What ExcelForm generates

=XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"Not found")

Common Mistakes to Avoid

Using XLOOKUP in Excel 2019 or older — it's only available in Excel 365 and Excel 2021+
Forgetting that lookup_array and return_array must be the same size
Not using the if_not_found argument — always add a fallback value like 'Not found' to avoid #N/A errors
Mismatching the size of return_array when spilling multiple columns

XLOOKUP vs VLOOKUP — Key Differences

XLOOKUP requires no column index number (error-prone in VLOOKUP), can return values to the left of the search column, supports wildcard matching natively, and has a built-in fallback for missing values — no IFERROR needed. If you're on Excel 365 or Google Sheets, use XLOOKUP. ExcelForm auto-selects XLOOKUP for Optimal and Advanced complexity settings.

Who Uses the XLOOKUP Formula Generator?

XLOOKUP is the most powerful lookup function in Excel 365 and Google Sheets, and ExcelForm's free XLOOKUP formula generator makes it accessible to anyone — whether you've never written a formula or just can't remember the argument order.

Finance analysts use XLOOKUP to replace complex VLOOKUP and INDEX MATCH combinations — fetching budget figures, account codes, and exchange rates from reference tables with cleaner syntax and native error handling. Data teams use XLOOKUP's ability to return multiple columns at once, eliminating repetitive nested formulas. HR teams use it to look up employee records from left-to-right or right-to-left — something VLOOKUP cannot do.

If you work in Excel 365, Excel 2021, or Google Sheets and you're still writing VLOOKUP, it's time to upgrade. XLOOKUP handles everything VLOOKUP does, plus reverse lookups, multi-column returns, wildcard matching, binary search, and cleaner error fallbacks. ExcelForm's AI generates the exact XLOOKUP syntax you need — including horizontal lookups and multi-criteria matching — from a plain English description.

XLOOKUP vs VLOOKUP: Key Differences

XLOOKUP was introduced in Excel 365 (2019) and Google Sheets (2022) specifically to fix VLOOKUP's limitations. Here is when to use each:

Use XLOOKUP when…

You have Excel 365, Excel 2021, or Google Sheets 2022+. You need to look left. You want cleaner error handling. You need to return multiple columns at once. You want wildcard matching built in.

Use VLOOKUP when…

Your file must open in Excel 2019 or older. You are sharing files with users who may not have Office 365. The lookup column is always the leftmost column and the formula is simple.

XLOOKUP full syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP With Multiple Criteria

To match on two or more columns simultaneously, use the & operator to combine lookup values and arrays:

XLOOKUP two-condition example

=XLOOKUP(E2&F2, A:A&B:B, C:C, "Not found")

This is significantly cleaner than the VLOOKUP+helper column workaround. ExcelForm generates this pattern automatically when you describe a multi-condition lookup.

FAQ

Frequently Asked Questions

How do I write an XLOOKUP formula from plain English?▾

Describe your lookup in the generator above — e.g. 'Find the manager name from my HR table where the employee ID matches B2'. ExcelForm writes the exact XLOOKUP with the correct array ranges and fallback value.

Does XLOOKUP work in Google Sheets?▾

Yes! Google Sheets added XLOOKUP support in 2022. ExcelForm generates XLOOKUP for Google Sheets when you select 'Google Sheets' or 'Both' in the environment dropdown.

Can XLOOKUP return multiple columns at once?▾

Yes — set the return_array to a multi-column range like Sheet2!B:D and XLOOKUP returns all three columns at once. This is one of XLOOKUP's most powerful features over VLOOKUP.

Is this XLOOKUP generator free?▾

Yes. ExcelForm is completely free — 10 AI-powered formula generations per day, no account required.

Does XLOOKUP work in Google Sheets?▾

Yes. XLOOKUP is available in Google Sheets as of 2022. The syntax is identical to Excel 365. If you are using an older version of Sheets that does not support XLOOKUP, use INDEX MATCH instead — ExcelForm's generator automatically falls back to INDEX MATCH when you select older compatibility modes.

How do I use XLOOKUP to return multiple columns at once?▾

XLOOKUP can return multiple columns by selecting a multi-column return_array: =XLOOKUP(A2, Sheet2!A:A, Sheet2!B:D). This returns columns B, C, and D for the matching row simultaneously — a capability VLOOKUP cannot match without multiple formula instances.

What does [if_not_found] do in XLOOKUP?▾

The [if_not_found] argument is the 4th parameter in XLOOKUP and replaces the need for IFERROR wrapping. Example: =XLOOKUP(A2, B:B, C:C, "Not found") returns 'Not found' instead of #N/A when no match exists. This makes formulas cleaner and easier to read than VLOOKUP with IFERROR.

Can XLOOKUP look left — return a column to the left of the lookup column?▾

Yes — this is one of XLOOKUP's biggest advantages over VLOOKUP. With VLOOKUP, your return column must be to the right of your lookup column. XLOOKUP has no such restriction: =XLOOKUP(D2, C:C, A:A) returns column A even though it is to the left of column C.

XLOOKUP Formula Generator