SheetDo
English
Home / Formulas / XLOOKUP
Lookup Excel + Google Sheets

XLOOKUP function in Excel

What is XLOOKUP in Excel? It searches a range for a value and returns a matching result from another range — no column counting, no VLOOKUP fragility. Available in Excel 365, Excel 2021, and Google Sheets.

XLOOKUP syntax & arguments

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

The first three arguments are enough for most exact-match lookups. Keep lookup_array and return_array the exact same height (or width for horizontal lookups).

1 lookup_value
Required

The value to find. It can be text, a number, a cell reference (e.g. E2), or the result of another formula.

2 lookup_array
Required

The row or column where Excel should search for lookup_value (e.g. A2:A6).

3 return_array
Required

The row, column, or table range to return from the matching position (e.g. C2:C6). Must match lookup_array dimensions.

4 if_not_found
Optional

The fallback result to return when no match is found (e.g. "Not found"). If omitted, Excel returns #N/A.

5 match_mode
Optional

Controls exact, approximate, or wildcard matching:

  • 0 — Exact match (default: returns a result only on exact match)
  • -1 — Exact or next smaller (useful for tiered rates/tax brackets)
  • 1 — Exact or next larger
  • 2 — Wildcard match (supports *, ?, ~)
6 search_mode
Optional

Controls search direction or binary search method:

  • 1 — First to last (default: searches top-down)
  • -1 — Last to first (searches bottom-up; perfect for finding the latest entry)
  • 2 — Binary ascending (requires sorted data)
  • -2 — Binary descending (requires reverse-sorted data)

Excel XLOOKUP example

Column A lists employee IDs, column B lists names, and column C lists salaries. Cell E2 contains the ID to find: E-102.

Employee IDNameSalary
E-101Ana72000
E-102Ben64000
E-103Chen81000
E-104Dia59000
E-105Eli77000
=XLOOKUP(E2, A2:A6, C2:C6, "Not found")

XLOOKUP searches A2:A6 for E-102 and returns 64000 from the same row in C2:C6. If the ID is missing, the formula returns Not found instead of #N/A.

How to use XLOOKUP in Excel

  1. Identify the lookup value and search column — Decide what you want to find (lookup_value, e.g. the employee ID in E2) and the row or column that contains those values (lookup_array, e.g. A2:A6).
  2. Point to the result column — Set return_array to the range holding the answer (e.g. C2:C6 for salaries). It must match the height of lookup_array exactly.
  3. Enter the formula and handle misses — Type =XLOOKUP(E2, A2:A6, C2:C6) and press Enter. Add "Not found" as the fourth argument so misses return friendly text instead of #N/A.

XLOOKUP with multiple criteria

Basic XLOOKUP matches a single value. To apply two or more conditions at once — say, find the salary for a given employee in a given department — multiply the boolean tests or join the values into one key:

Method 1: boolean multiplication (recommended)

=XLOOKUP(1, (A2:A6=E2) * (B2:B6=F2), C2:C6, "Not found")

Each test returns TRUE/FALSE; multiplying them leaves 1 only where every condition is true, and XLOOKUP finds that row.

Method 2: concatenation

=XLOOKUP(E2&"|"&F2, A2:A6&"|"&B2:B6, C2:C6, "Not found")

Join criteria and ranges with a separator (&) so both sides form one combined lookup key. The same patterns also power two-way lookups that match a row and a column at once.

XLOOKUP vs VLOOKUP

XLOOKUP

Look up left or right. No column index number to count. Default exact match. Clean built-in not-found fallback.

VLOOKUP

Look up only to the right. Fragile when columns move or insert. Easy to forget the FALSE / 0 exact-match flag.

XLOOKUP vs INDEX MATCH

XLOOKUP

One function instead of two. Built-in if_not_found — no wrapping IFERROR. Identical syntax for left, right, or last-match lookups.

INDEX MATCH

Runs in every Excel version, including pre-2021 releases without XLOOKUP. Handles two-way lookups naturally with a row MATCH and a column MATCH inside INDEX.

Common mistakes & caveats

  • #N/A — No exact match found; check trailing spaces, leading zeros, or text-vs-number formatting.
  • #VALUE! — lookup_array and return_array have different lengths or dimensions.
  • Fill formula down errors — Remember to lock ranges with $ (e.g. $A$2:$A$6) before dragging.

Intro XLOOKUP practice problem

Solve the problem directly in this locked Mini sheet with instant grading, or open it on its dedicated page.

Open full problem →
Description
Hints 0/4
Solutions 3
–
Test case
1
2
3
Reset
Submit
fx
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
Loading interactive spreadsheet...
Preparing grid cells and formula calculation engine
○ Ready Enter formula in highlighted cells, then click Submit.

Related practice problems

Practice combining XLOOKUP and logic formulas in realistic, less-prescriptive spreadsheet challenges.

Medium Math

Calculate 5-year CAGR in Excel

A friend asks you to evaluate an investment: it grew from the start value in B1 to the end value in B2 over the years in B3, and they want its annualized rate to compare with other options. Fill the CAGR cell B4 with the compound annual growth rate. Return the rate as a decimal (for example 0.1 for 10%).
Hard Logic

Calculate tiered sales commission (IF)

Quarter-end is here and sales ops needs commission run for the team in B2:B5. The plan has three tiers: 8% on sales of at least 10,000, 5% on sales of at least 5,000, and 3% below that. Fill the Commission cells D2:D5 with each person's payout.
Easy Text

Join first and last names (CONCAT)

The payroll system imported first names into column A and last names into column B, but the email template needs one full-name field. In Full name, write a formula that joins A2 and B2 with a single space — C2 should read like Alice Wang. Start in C2, then fill down to C5.
Easy Math

Average three exam scores

You're a teacher wrapping up an exam: the scores for Alex, Betty, and Chris are already in B2:B4, and the report still needs the class average. Fill the Average cell B5 with the mean of the three scores.
Easy Lookup & Reference

Flag a double-booked order (COUNTIF)

A customer swears they were charged twice, and the order log looks suspicious: order ID ORD-1042 might have been booked more than once. Before refunding anything, count how many rows in A2:A11 carry that exact ID. The disputed ID is already sitting in D1. Put the answer in the Occurrences cell D2.
Easy Math

Count who actually signed up (COUNTA)

Ten colleagues were invited to a workshop and the sign-up sheet is in B2:B11 — some wrote their name, some left the cell blank. The venue only fits confirmed attendees, so count how many cells in B2:B11 are not empty. Put the headcount in the Confirmed cell D2.
Easy Logic

Count passing scores (COUNTIF)

You just finished grading an exam for an adult-education class. The passing line is 60 points, and the grade report needs one number at the top: how many of the eight students in B2:B9 passed. Fill the Passed count cell D2.
Medium Logic

Check if cell contains specific text (IF + SEARCH)

Customer support needs to triage incoming tickets in A2:A5. If a ticket's message contains the word Urgent (case-insensitive), label it as Priority; otherwise, label it as Normal. Fill the Priority status cells B2:B5 using an IF formula that checks for substring presence.
Easy Logic

Unit prices that survive bad data (IFERROR)

An order's unit price is Total divided by Qty — until someone types 0 as the quantity and every division in the report turns into #DIV/0!. Build the safe version in Unit price cell D2: show the normal price when the math works, and the text "Check qty" when the division blows up.
Medium Data Cleaning

Handle missing SKUs in a lookup (IFERROR)

Customer service forwarded an order list, and one of the SKUs was discontinued — it is not in the price list anymore, so a plain lookup would blow up with an error in the middle of your report. Fill the Unit price cells F2:F4 so every order row either shows the matched price or the text Not found.
Hard Lookup & Reference

Find who earns a given salary (INDEX + MATCH)

Payroll flagged one salary amount for audit, but the query came back as a number — you need the person. The roster keeps names in the first column and salaries in the last, and the amount to trace sits in E2. Return the matching employee's name in the Employee cell F2.
Medium Logic

Audit missing SKUs with ISNA

Before the price report ships, QA wants a simple audit answer for the SKU in E2: is it missing from the price list in A2:A6 or not? Put TRUE (missing) or FALSE (present) in the Missing? cell D2. This is an audit question — you want the TRUE/FALSE itself, not a wrapped-up message.
Easy Text

Extract a category code (LEFT)

Your inventory sheet packs everything into one product code like PRD-1023: the first three letters are the category, the rest is the serial. Before you can build a category filter, the report needs a separate Category code column. Fill C2:C5 from the codes in A2:A5.
Easy Text

Count product code characters (LEN)

Shipping labels only fit a fixed number of characters, and every product code in A2:A5 is a different length — PRD-1023 is not the same width as SKU-9. Fill Code length in C2:C5 with how many characters each code contains. The dash counts.
Easy Text

Extract the serial from a product code (MID)

Warehouse labels use one fixed format, PRD-1023-A: three letters, a dash, a four-digit serial, another dash, and a suffix letter. The packing report only needs the serial — characters 5 through 8. Fill Serial in C2:C5 from the codes in A2:A5.
Easy Math

Multiply quantity × unit price (* / PRODUCT)

An order sheet lists quantity in A2:A5 and unit price in B2:B5. Fill Line total in C2:C5 with quantity times price for each row. Excel has no dedicated “multiply function” — use * or PRODUCT.
Medium Logic

Convert a score into a letter grade (nested IF)

It's report-card season: exam scores are already entered, and the school system needs a letter grade beside each one. Fill the Letter cell B2 with a grade for the score in A2: A for scores ≥90, B for ≥80, C for ≥70, otherwise D.
Medium Lookup & Reference

XLOOKUP misses without blowing up (IFERROR)

Customer service keeps typing SKUs that stopped existing, and every miss splatters #N/A across the price report. Look up the price for the SKU in E2 from the list in A2:C6, and return the text "Not found" instead of an error. Fill the Price cell F2 — two ways solve this, and one of them doesn't even need IFERROR.
Easy Math

Round unit prices to whole numbers (ROUND)

A price list stores messy decimals in A2:A5. Finance wants whole-number amounts for the invoice. Fill Rounded in B2:B5 by rounding each price to 0 decimal places — half goes away from zero, same as Excel ROUND.
Easy Text

Capitalize customer names (PROPER)

A partner exported their CRM with no data hygiene: customer names in A2:A5 arrive as john smith, MARY JOHNSON and worse. Before the names go into the quarterly report, fill Proper name with a PROPER formula so every name is capitalized cleanly, starting in C2 and filling down to C5.
Medium Logic

Compare a score to the middle value

You're analyzing a class quiz: the five scores are already entered in A2:A6, and you want to know whether the first student's score in A2 sits above, below, or right at the middle of the class. Fill the vs Middle cell B2 — return Above, Below, or Equal.
Easy Math

Sum A1 and A2 with a formula

Your shop just opened and its first two months of revenue are recorded in A1 and A2. Fill the output cell A3 with the total revenue for both months.
Medium Math

Total sales for one region (SUMIF)

The quarterly ledger in A2:B7 mixes four regions into one running list. Regional review is tomorrow and the North team wants their number, not the company total. Put the North-region sales total in the Total sales cell E2, driven by the region name in D2.
Medium Math

Regional sales with two filters (SUMIFS)

You run the numbers for a tea chain. The East-region manager asks: how much Oolong revenue did East actually book? The log is in A2:C11 — Region in A, Product in B, Amount in C — and the requested region is in E1. Total only the rows that are East AND Oolong, and put the sum in the East Oolong total cell E2.
Medium Text

Build a mailing list in one cell (TEXTJOIN)

Marketing needs the newsletter recipients as one paste-ready line for the email tool — not a column to copy cell by cell. The addresses sit in A2:A5. Combine them into a single semicolon-separated line in the Mailing list cell C2.
Easy Data Cleaning

Clean extra spaces from imported names (TRIM)

A CRM export landed in your inbox, and every customer name carries stray spaces — leading, trailing, even doubles in the middle. Your mail merge will bounce on all of them. Fill the Cleaned name cells C2:C5 so each name from A2:A5 is trimmed to a single normal form.
Medium Data Cleaning

Remove duplicate contacts (UNIQUE)

Your marketing sheet collects signups from three forms, so the same contact shows up several times in A2:A7. The email tool needs a clean list: put a UNIQUE formula in Unique list so the first unique contact lands in C2 — the full deduplicated list then spills straight down.
Medium Lookup & Reference

Find tax bracket using approximate match (VLOOKUP)

HR needs to compute the withholding tax rate for each employee's annual taxable income. The reference table in columns A–B lists income thresholds in ascending order (0, $30,000, $60,000, $100,000) and their corresponding tax rates. Fill the Tax rate cells F2:F4 using VLOOKUP with approximate match so each employee is assigned the rate matching their income tier in E2:E4.
Easy Lookup & Reference

Look up an employee's department (VLOOKUP)

You're in an HR screen and the interviewer slides over an employee directory: IDs, names and departments in A2:C5. Given the ID in E2, write a formula in the Department cell F2 that reports which department that employee works in.
Easy Lookup & Reference

Look up unit price by SKU (VLOOKUP)

You run a small warehouse. The price list on the left has every SKU, but the order sheet on the right still needs unit prices. Fill the Unit price cells F2:F4 so each order row pulls the price for its SKU from the price list in columns A–C.
Medium Lookup & Reference

Pull multiple columns for an employee (VLOOKUP)

You are building an employee profile card. Given an employee ID in F2, pull their Department into G2 and City into H2 from the master employee directory in columns A–D. Lock the table reference with $ so the lookup formula remains stable across columns.
Medium Math

Weighted final grade with SUMPRODUCT

Your course grade is not a plain average — the final exam counts for more than the homework. The grading sheet in A2:C5 lists each component's score and its weight (weights sum to 1). Compute the final grade in the Final grade cell E2 by pairing each score with its weight in one formula.
Medium Lookup & Reference

Default to zero on missing discount (VLOOKUP + IFERROR)

You are processing store checkouts. Members get a discount based on their VIP tier in columns A–B (Bronze 5%, Silver 10%, Gold 15%, Platinum 20%). Non-VIP customers have standard tiers like 'Regular' or 'None' that are NOT in the VIP table, causing a naked VLOOKUP to fail with #N/A. Fill the Discount cells E2:E5 using VLOOKUP wrapped in IFERROR (or IFNA) so missing tiers safely default to 0.
Easy Text

Show the weekday from a date (TEXT / WEEKDAY)

A schedule sheet lists dates in A2:A5, but the report needs the weekday name next to each one. Fill Weekday in B2:B5 so each row shows an English abbreviation such as Wed or Mon. Use a formula that follows the date in column A — a hardcoded Wed fails as soon as A2 changes.

FAQ

What is the XLOOKUP function in Excel?

XLOOKUP is a lookup function available in Excel 365/2021 and Google Sheets. It searches a range (lookup_array) for a value and returns the item at the same position from another range (return_array), replacing both VLOOKUP and HLOOKUP.

What is the basic XLOOKUP syntax?

Use =XLOOKUP(lookup_value, lookup_array, return_array). The first three arguments cover most exact-match lookups.

What's the difference between VLOOKUP and XLOOKUP?

XLOOKUP can look left or right, needs no column index number, defaults to exact match, and includes a built-in if_not_found argument. VLOOKUP only looks right and breaks when columns are inserted or moved.

Is XLOOKUP better than VLOOKUP?

For most modern workbooks, yes. XLOOKUP can look left or right, needs no column index, defaults to exact match, and supports a custom not-found message.

XLOOKUP vs INDEX MATCH: which is better?

XLOOKUP is simpler — one function with a built-in not-found fallback. INDEX MATCH is still the right choice for Excel versions older than 2021, or for classic two-way lookups that match a row and a column at once.

Does XLOOKUP work in Google Sheets?

Yes. Google Sheets supports XLOOKUP with the same six-argument syntax, including if_not_found, match_mode, and search_mode.

How do I use XLOOKUP with multiple criteria?

Multiply boolean tests: =XLOOKUP(1, (A2:A6=E2)*(B2:B6=F2), C2:C6). Or concatenate the criteria into one key: =XLOOKUP(E2&F2, A2:A6&B2:B6, C2:C6).

Why do I get #N/A with XLOOKUP?

Usually there is no exact match. Check trailing spaces, text-vs-number mismatches, or confirm both arrays are the same height.