Skip to content
BENIBIX Learn

XLOOKUP Function

Business🗂️ Tools & SoftwareOffice Software

Also known as XLOOKUP, Microsoft Excel XLOOKUP

An Excel formula that finds a code in one column. It gives back what sits on that same row.

Think of it like

It is like finding a name on a class list. Then you read the phone number on the same line.

Example

Lucy has 500 staff IDs in column A and email addresses in column D. She types an ID into F2, and =XLOOKUP(F2, A2:A501, D2:D501) shows that person's email.

Why it matters

Joining two lists by a shared code is one of the most common jobs in a spreadsheet. XLOOKUP does it in one short formula.

Definition

XLOOKUP is an Excel formula that finds a value in one column and returns its match from another. You give it three things: what to find, where to look and what to bring back. It looks for an exact match unless you ask for something else. If nothing matches, it shows #N/A, or a message you choose. The answer column can sit to the left or the right of the search column. It works in Excel for Microsoft 365, Excel 2021 and later, and Excel on the web.

Example code

excel
     A        B          C
1    Code     Item       Price
2    P-101    Pencil     0.80
3    P-102    Notebook   4.50
4    P-103    Ruler      1.20

=XLOOKUP("P-102", A2:A4, C2:C4, "Not found")
4.50
It finds P-102 in column A, then returns 4.50 from the same row of column C.
Down the first column to the match, then across the same row to the answer.

Common mistake

Leaving out the dollar signs, like $A$2:$A$501, before copying the formula down. The ranges then slide down a row each time, and later rows miss part of the list.

Key takeaways

  1. 1
    Three inputs. What to find, where to look, what to return.
  2. 2
    Exact by default. No match shows #N/A or your own message.
  3. 3
    Either side. The answer column can sit left or right.