Excel Lookup and Reference Functions: A Practical Guide

Excel lookup functions search for a key—such as a product ID or employee number—and return related information from the same record. Reference functions solve a different but connected problem: they return a position, value, or cell reference that another formula can use.

For most new workbooks, XLOOKUP is the clearest choice for a single match. FILTER is better when you need every matching record, while INDEX with XMATCH is useful for position-based and two-way lookups. VLOOKUP, HLOOKUP, and MATCH remain important when a workbook must work in older Excel versions.

The formulas in this guide use commas between arguments. Depending on your regional settings, Excel may use semicolons instead. Follow the separator shown by Formula AutoComplete.

Choose the lookup method by the result you need

Microsoft’s lookup and reference functions list includes the complete category. The sections below concentrate on the choices that matter most when building and troubleshooting everyday lookups.

Prepare the lookup data first

Assume cells A1:D7 contain this product table:

Before writing a formula, check four details:

• The lookup key uses the same data type on both sides. A number and a number stored as text are not the same value.
• Text keys do not contain accidental leading, trailing, or nonprinting characters.
• The lookup and return ranges cover corresponding rows and have compatible dimensions.
• The key is unique if the formula is supposed to return one authoritative record.

These checks prevent many formulas that are syntactically correct from returning #N/A or the wrong record.

Use XLOOKUP for a direct exact match

Enter a product ID such as P-116 in G2. The following formula searches A2:A7 and returns the corresponding price from D2:D7:

=XLOOKUP(G2,A2:A7,D2:D7,"Not found")

Use XLOOKUP to find the unit price for product ID P-116 by searching the Product ID column and returning the matching value from Unit Price.

The returned value is 24; a two-decimal number format displays it as 24.00. The four arguments mean:

1. Choose the lookup value. G2 contains the product ID to find.
2. Choose the lookup array. A2:A7 contains the product IDs.
3. Choose the return array. D2:D7 contains the prices to return.
4. Define the no-match result. "Not found" appears instead of #N/A.

XLOOKUP uses exact matching when its optional match mode is omitted. It can return from a column on either side of the lookup column because the lookup and return arrays are independent. Microsoft documents the full syntax as:

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

The official XLOOKUP reference also explains wildcard, approximate, and reverse-search modes.

Return several columns with one formula

A wider return array can provide the product name, region, and price at once:

=XLOOKUP(G2,A2:A7,B2:D7,"Not found")

The three results spill across neighboring cells. Keep those cells empty; blocked output produces a #SPILL! error.

Choose which duplicate match to return

By default, XLOOKUP searches from the first item to the last and returns the first match. To search from the bottom upward and return the last match, set search_mode to -1:

=XLOOKUP(G2,A2:A7,D2:D7,"Not found",0,-1)

Choosing the first or last duplicate does not resolve inconsistent records. If a key should be unique, investigate the duplicates rather than silently accepting one.

Use VLOOKUP when compatibility matters

VLOOKUP searches the first column of a table array and returns a value from a numbered column in the same row. Using the product table, this exact-match formula returns the price for the ID in G2:

=VLOOKUP(G2,A2:D7,4,FALSE)

This VLOOKUP example searches for product ID P-116 in the first column of the table and returns the matching unit price from the fourth column.

The number 4 tells Excel to return the fourth column of A2:D7. FALSE requests an exact match.

VLOOKUP has two structural limits: the lookup column must be the first column in the table array, and the return column is identified by a position number. If the layout changes, that hard-coded column number may need attention. XLOOKUP and INDEX/MATCH use separate lookup and return ranges, so they do not have the leftmost-column restriction.

In VLOOKUP, omitting the fourth argument defaults to approximate matching. Use FALSE explicitly for ordinary IDs, names, and codes. Microsoft’s VLOOKUP reference confirms the argument order and default.

HLOOKUP follows the same idea in a horizontal layout: it searches the top row and returns from a row number you specify. For a new workbook in a version that supports it, XLOOKUP can handle vertical or horizontal ranges without a separate function.

Combine INDEX with XMATCH or MATCH

XMATCH returns the relative position of a value rather than the related value itself. If G2 contains P-116, this returns 4 because that ID is fourth in A2:A7:

=XMATCH(G2,A2:A7)

INDEX can use that position to retrieve the fourth value from the price range:

=INDEX(D2:D7,XMATCH(G2,A2:A7))

This formula combines INDEX and XMATCH to find product ID P-116 and return the matching unit price from the Unit Price column.

The returned value is 24. XMATCH defaults to an exact match and can search from last to first. Its optional modes are described in Microsoft’s XMATCH reference.

For Excel 2016 or 2019, replace XMATCH with MATCH and specify 0 for an exact match:

=INDEX(D2:D7,MATCH(G2,A2:A7,0))

The zero matters. The default MATCH mode is approximate, whereas XMATCH defaults to exact matching. Microsoft’s MATCH documentation lists the sorting requirements for its approximate modes.

Build a two-way lookup with INDEX and XMATCH

A two-way lookup finds both a row and a column. Assume A10:D13 contains this quarterly sales table:

If G10 contains Desk Lamp and G11 contains Q2, use:

=INDEX(B11:D13,XMATCH(G10,A11:A13),XMATCH(G11,B10:D10))

This two-way lookup uses one XMATCH to find the Desk Lamp row and another XMATCH to find the Q2 column, then INDEX returns the intersecting value of 74.

The first XMATCH returns row position 2. The second returns column position 2. INDEX returns the value at that intersection: 74. This mirrors the row-and-column form described in Microsoft’s INDEX reference.

Return every match with FILTER

A lookup normally returns one record. If G2 contains West and you need every product assigned to that region, use FILTER:

=FILTER(A2:D7,C2:C7=G2,"No matches")

The FILTER function returns every row where the Region column matches West, spilling the matching product records into adjacent cells.

The formula returns both the Ceramic Mug and Water Bottle rows and spills the results into adjacent cells. The third argument supplies a message when no rows qualify. The FILTER reference explains its Boolean include array and spill behavior.

Use XLOOKUP when one matching result is correct. Use FILTER when all matches are meaningful. That decision should come from the data model, not from whichever formula is shorter.

Use approximate matching only for defined bands

Approximate matching is appropriate for ordered thresholds such as commission bands, shipping tiers, or grading scales. Suppose J2:K6 contains minimum sales and discount rates:

If G2 contains sales of 3200, this formula requests an exact match or the next smaller threshold:

=XLOOKUP(G2,J2:J6,K2:K6,"Below minimum",-1)

This XLOOKUP formula returns the next smaller matching threshold, so sales of 3200 receive the 5% discount assigned to the 2500 sales band.

The result is 5%, the rate for the 2500 threshold. A compatible VLOOKUP formula is:

=VLOOKUP(G2,J2:K6,2,TRUE)

For approximate VLOOKUP, keep the first column in ascending order. An unsorted band table can produce a plausible but incorrect result. Exact matching should remain the default choice for identifiers and names.

Understand the reference side of the category

Not every function in the Lookup and Reference category searches a table. Some report information about a reference or select a value by position:

INDIRECT can turn text such as "C10" into a reference, and OFFSET can return a reference shifted from a starting cell. These functions are useful when a genuinely dynamic reference is required, but a direct reference is usually easier to read and audit.

Handle lookup errors without hiding real problems

XLOOKUP has a built-in if_not_found argument. For a legacy formula, IFNA can replace only the expected #N/A result:

=IFNA(INDEX(D2:D7,MATCH(G2,A2:A7,0)),"Not found")

This formula wraps INDEX and MATCH in IFNA so a missing product ID returns Not found instead of a #N/A error.

This is narrower than wrapping the formula in IFERROR, which can conceal unrelated problems such as an invalid reference. Microsoft’s IFNA reference confirms that it handles the #N/A error specifically.

Check version compatibility before sharing

Microsoft states that XLOOKUP is not available in Excel 2016 or Excel 2019. A workbook created in a newer version may therefore show an unsupported-function result when opened in those releases.

Frequently asked questions

Make the lookup rule explicit

A reliable lookup states three things clearly: the key to find, the field to return, and the rule for unmatched or duplicate data. XLOOKUP expresses those choices directly in current Excel. INDEX with XMATCH handles position-based and two-way work, while FILTER covers one-to-many results. Legacy functions remain valuable when compatibility is part of the requirement.

Whichever method you choose, test missing keys, duplicates, mixed data types, and approximate-match boundaries. The formula is only as dependable as the lookup rule and the source data behind it.