Every Excel LOOKUP Explained

Excel has six common lookup options: LOOKUP, VLOOKUP, HLOOKUP, INDEX/MATCH, XLOOKUP, and XMATCH. For new work, start with XLOOKUP: =XLOOKUP(A2,CustomerIDs,CustomerNames,"Not found"). Use the older functions when you need compatibility, a specific approximate-match behavior, or an existing workbook already depends on them.

The downloadable guide is available early because it belongs with the decision, not at the end of the page: download the Ultimate Excel Lookup Guide PDF.

Excel Lookup Functions Compared

If your goal is to modernize formulas, read XLOOKUP vs VLOOKUP next. If you want a deeper XLOOKUP walkthrough, use the dedicated Excel XLOOKUP guide.

Which Lookup Function Should You Choose?

Choose by workbook age, lookup direction, and whether the match must be exact.

XLOOKUP

XLOOKUP is the best default lookup function in modern Excel because it separates the lookup range from the return range.

=XLOOKUP(A2,$A$2:$A$100,$D$2:$D$100,"Not found")

This searches for A2 in A2:A100 and returns the matching value from D2:D100. It does not care whether the return column is left or right of the lookup column.

Useful XLOOKUP arguments:

For most business workbooks, this is the formula to teach first.

VLOOKUP

VLOOKUP searches the first column of a table and returns a value from a column to the right.

=VLOOKUP(A2,$A$2:$D$100,3,FALSE)

This searches for A2 in the first column of A2:D100 and returns the value from the third column of that table. The final FALSE requests an exact match.

Use VLOOKUP when the workbook must remain familiar to users who know older Excel. Avoid it when the return column might move, because hard-coded column numbers are a common source of errors. For performance and exact-vs-approximate details, see the advanced VLOOKUP tutorial.

HLOOKUP

HLOOKUP is the horizontal version of VLOOKUP.

=HLOOKUP(A2,$B$1:$M$4,3,FALSE)

It searches the top row of B1:M4 and returns a value from the third row of that range. Use it for wide tables where months, product codes, or scenario names run across the top.

In new workbooks, XLOOKUP is usually clearer:

=XLOOKUP(A2,$B$1:$M$1,$B$3:$M$3,"Not found")

INDEX and MATCH

INDEX/MATCH is still valuable when you need modern lookup behavior in a workbook that does not support XLOOKUP.

=INDEX($D$2:$D$100,MATCH(A2,$A$2:$A$100,0))

MATCH finds the relative row position of A2 in A2:A100. INDEX returns the value from that same position in D2:D100.

Use INDEX/MATCH when:

• The return range is to the left of the lookup range.
• The workbook must support older Excel versions.
• Existing models already use INDEX/MATCH and consistency matters.

XMATCH

XMATCH returns a position rather than a value.

=XMATCH(A2,$A$2:$A$100,0)

That formula returns the relative position of A2 inside A2:A100. It is useful when you want to feed a row or column number into INDEX, build dynamic reports, or locate a matching header.

Combined with INDEX, it can replace older MATCH formulas:

=INDEX($D$2:$D$100,XMATCH(A2,$A$2:$A$100,0))

LOOKUP

The original LOOKUP function is best treated as a legacy approximate-match tool. It comes in vector and array forms, but the vector form is the one worth knowing.

=LOOKUP(A2,$F$2:$F$6,$G$2:$G$6)

This searches sorted thresholds in F2:F6 and returns the corresponding result from G2:G6. It finds the largest value less than or equal to the lookup value.

Use LOOKUP only when the lookup vector is sorted ascending. If the data is unsorted, use XLOOKUP exact match or INDEX/MATCH exact match instead.

Approximate Match Examples

Approximate match is not wrong; it is just specific. Use it for brackets and thresholds:

For a score in A2, either of these formulas can return the grade:

=XLOOKUP(A2,$F$2:$F$6,$G$2:$G$6,,-1)

=LOOKUP(A2,$F$2:$F$6,$G$2:$G$6)

The table must be sorted from smallest threshold to largest. For more examples, see Excel Approximate Match.

Common Lookup Errors

Most lookup errors come from one of five causes:

When in doubt, test the lookup value with =COUNTIF(lookup_range,A2). If the count is zero, Excel is not seeing an exact match.

Frequently Asked Questions

How do you use LOOKUP in Excel?

Use =LOOKUP(lookup_value,lookup_vector,result_vector) with a sorted ascending lookup vector. It returns the result paired with the largest lookup value less than or equal to the search value.

How do you use VLOOKUP in Excel?

Use =VLOOKUP(lookup_value,table_array,col_index_num,FALSE) for an exact match. The lookup column must be the first column of the table array, and the return column must be to the right.

How do you use XLOOKUP in Excel?

Use =XLOOKUP(lookup_value,lookup_array,return_array,"Not found"). The lookup and return arrays can be separate ranges, so the return value can be left, right, above, or below the lookup value.

What is the difference between VLOOKUP, HLOOKUP, and XLOOKUP?

VLOOKUP searches vertically, HLOOKUP searches horizontally, and XLOOKUP can search either direction while also handling missing values and left-side returns more cleanly.

Can Excel do fuzzy lookup?

Excel formulas can do approximate numeric matching, but true fuzzy text matching is better handled with Power Query’s fuzzy merge or Microsoft’s Fuzzy Lookup add-in. Do not use approximate numeric match as a substitute for fuzzy text matching.

How do you look up data from another sheet?

Add the sheet name to the range references:

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

How do you return multiple values?

With XLOOKUP, point the return array at multiple columns:

=XLOOKUP(A2,$A$2:$A$100,$C$2:$E$100,"Not found")

This can spill the matching row’s return values across adjacent cells in dynamic-array Excel.