Excel Lookup Formula Examples for Common Tasks

Excel lookup formulas find a value in one range and return a related value from another. In Microsoft 365, a straightforward exact-match example is =XLOOKUP(E2,A2:A6,C2:C6,"Not found",0): it searches for the value in E2 in A2:A6 and returns the corresponding entry from C2:C6, or displays “Not found” if there is no match.

What a lookup formula does

A lookup connects a search value—such as a product code, employee name, or date—to information stored elsewhere. A typical table might have product codes in column A, product names in B, and prices in C. If E2 contains a code, a lookup can return the matching price without requiring you to scan the table manually.

Most lookup formulas have three essential parts: the value to find, the range or array to search, and the range or array that contains the answer. The match rule matters too. An exact match is usually appropriate for identifiers such as invoice numbers, names, and SKUs. Approximate matching is useful for ordered bands such as tax brackets, commission tiers, or shipping thresholds, provided the lookup table is arranged correctly.

Before entering a formula, check that the search and return ranges line up. If the second item in the search range is the match, the formula should return the second corresponding item in the result range. Also check whether values that look alike are stored in the same way: the number 123 and text “123” may not match as expected.

Use XLOOKUP for a modern exact match

XLOOKUP is usually the clearest choice in Microsoft 365 because the search range and return range are separate arguments. It can return a value from a column on either side of the search column, and it uses exact matching by default. The final zero below makes the exact-match requirement explicit.

With product codes in A2:A6, product names in B2:B6, prices in C2:C6, and the code to search in E2, use =XLOOKUP(E2,A2:A6,C2:C6,"Not found",0). If E2 contains P-104 and that code is on row 4 with a price of 18.50 in C4, the formula returns 18.50. If E2 contains a code absent from A2:A6, it returns the text Not found rather than an error.

The optional fourth argument controls what happens when Excel cannot find the search value. Supplying a message makes a sheet more understandable than showing #N/A, especially when a missing record is an expected possibility. Do not use a message to hide a data problem that needs investigation; it can be useful to distinguish a normal missing result from a broken or incomplete source table.

XLOOKUP also supports searching from the bottom upward. With employee IDs in A2:A6, department entries in C2:C6, and the ID to find in E2, use =XLOOKUP(E2,A2:A6,C2:C6,"Not found",0,-1). If an ID appears more than once, the formula returns the department from its last occurrence. This is handy for finding the latest row in a simple log, although a proper date-based or structured data model may be safer when records can be reordered.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available in Excel 2019 and earlier, so a workbook using it may show a function-name error for someone opening it in an older release.

Use VLOOKUP with a table arranged in columns

VLOOKUP remains common in existing workbooks and is useful when the search key is in the leftmost column of a table. It searches down that first column and returns a value from a numbered column to its right. For an exact match, include FALSE as the fourth argument; leaving that argument out can lead to approximate-match behavior.

With product codes in A2:A6, names in B2:B6, prices in C2:C6, and the code to search in E2, use =VLOOKUP(E2,A2:C6,3,FALSE). If E2 is P-104 and the matching row has 18.50 in the third column of the table, the result is 18.50. The number 3 counts columns from the left edge of A2:C6, so column C is the third column.

A key limitation is that VLOOKUP cannot return a value from a column to the left of its search column. Its column number is also easy to break: inserting or deleting columns inside the table can change which field a number refers to. Use absolute references such as $A$2:$C$6 if you plan to copy a formula and want the table range to stay fixed. XLOOKUP avoids the numbered-column limitation by naming the return range directly.

Use INDEX and MATCH for flexible older-workbook lookups

INDEX and MATCH are a reliable option when a workbook needs to work in Excel versions that predate XLOOKUP. MATCH finds the position of a value in a range, and INDEX returns the item at that position in a separate range. Using zero as MATCH’s third argument requests an exact match.

With product codes in A2:A6, prices in C2:C6, and the code to find in E2, enter =INDEX(C2:C6,MATCH(E2,A2:A6,0)). If P-104 is the third item in A2:A6 and the third corresponding price in C2:C6 is 18.50, the result is 18.50. Unlike VLOOKUP, this arrangement does not require the return column to be to the right of the search column.

Keeping the ranges the same height is important: MATCH returns a position within A2:A6, and INDEX uses that position within C2:C6. If one range starts on row 2 and the other starts on row 3, the formula can return a value from the wrong record even though it calculates without an error. INDEX and MATCH are also useful when you want to keep the search and return logic visible as separate parts of a formula.

Find a value in a range or tier table

Approximate lookup is for choosing the closest eligible threshold, not for loosely matching an identifier. For example, a commission table can list minimum sales values in ascending order and the corresponding rate beside each value. With thresholds 0, 50, 100, and 200 in A2:A5, rates in B2:B5, and sales of 87 in E2, use =XLOOKUP(E2,A2:A5,B2:B5,"No tier",-1). The -1 match mode means exact match or next smaller value, so the formula selects the row for 50 and returns the rate in B3.

This approach assumes the thresholds and rates are paired correctly. XLOOKUP with match mode -1 does not require the same ascending-sort rule as the older LOOKUP function, but arranging a tier table in ascending order still makes it easier to inspect. Values below the smallest threshold have no next-smaller match, so decide how the workbook should handle that case—perhaps by adding an appropriate starting threshold or using the not-found result.

For older Excel files, LOOKUP can perform an approximate search. With the same ascending thresholds in A2:A5, rates in B2:B5, and 87 in E2, use =LOOKUP(E2,A2:A5,B2:B5). It returns the rate paired with the largest threshold less than or equal to 87, which is the value in B3. LOOKUP expects the search values to be sorted in ascending order; unsorted data can produce a misleading result. For exact-match tasks, prefer an explicitly exact method instead.

Match on two conditions or find a row and column

Sometimes one field is not enough to identify a record. A person’s name may appear in more than one department, for example, so the lookup needs both the name and department. With names in A2:A6, departments in B2:B6, results in C2:C6, the requested name in G2, and the requested department in G3, use =XLOOKUP(1,(A2:A6=G2)*(B2:B6=G3),C2:C6,"Not found"). The comparisons create arrays of TRUE and FALSE values; multiplying them makes a 1 where both conditions are true. XLOOKUP finds that 1 and returns the corresponding entry from C2:C6.

If multiple rows satisfy both conditions, this formula returns the first matching row. If the two fields are not unique together, add another condition or use a method designed to return every matching record. In Microsoft 365, FILTER can return multiple matches, but it spills results into neighboring cells, so those cells need to be clear. XLOOKUP, FILTER, and the array behavior in this example are available in Microsoft 365 and Excel 2021 or later.

A related task is a two-way lookup: select a row by one label and a column by another. With regions in A2:A4, product headings in B1:D1, values in B2:D4, the region to find in G2, and the product heading in G3, use =INDEX(B2:D4,MATCH(G2,A2:A4,0),MATCH(G3,B1:D1,0)). If G2 is West and G3 is Coffee, the formula returns the value at the intersection of the West row and Coffee column—for example, 240 if that cell contains 240. The first MATCH supplies the row position; the second supplies the column position.

Common lookup errors and how to prevent them

A lookup that seems correct can still return an error or an unexpected result. Check these issues before rewriting the formula:

Wrong match mode: Use exact matching for codes and names. In VLOOKUP, specify FALSE; in XLOOKUP, exact match is the default, but an explicit 0 documents the intent.
Numbers stored as text: A cell displaying 00125 may contain text while the lookup value is numeric, or the reverse. Standardize the data type rather than adding increasingly complicated workarounds.
Extra spaces or inconsistent spelling: A trailing space can make two visible labels different. Clean source data where possible and check punctuation, capitalization, and spelling.
Duplicate keys: A single-result lookup returns one matching record, not a warning that duplicates exist. Decide whether the first or last match is appropriate, or make the key unique.
Ranges shifted while copying: Relative references move when a formula is filled down. Lock table ranges with dollar signs when they should remain fixed, while leaving the lookup cell relative if each row should search for a different value.
Missing records confused with formula problems: #N/A can indicate no match, while errors such as #REF! often point to an invalid reference. A custom not-found message can clarify expected misses, but inspect the underlying data when a result should exist.

When debugging, test a single known record and inspect the exact cells used by the formula. Confirm that the search value appears in the specified range and that the return range contains the expected value on the corresponding row. For approximate searches, also verify the order and meaning of the thresholds.

Choose the right lookup formula

For a new workbook in Microsoft 365, XLOOKUP is a strong default for a one-result lookup because its arguments are readable, it supports exact matches and reverse searches, and the return range can be on either side of the search range. For a legacy workbook, VLOOKUP may be simplest when the search key is the first column and the result is to its right. INDEX and MATCH offer more flexibility in older Excel versions.

Use approximate matching only when the business rule really is “exact value or nearest lower threshold,” and document that rule near the table. Use a two-way INDEX/MATCH when both a row label and a column label determine the answer. When conditions identify several records rather than one, consider a filter or a PivotTable instead of forcing a single-result formula to represent multiple answers.

For reliable workbooks, use clear headings, keep source data in consistent columns, avoid merged cells in lookup tables, and make missing-result handling intentional. If the file will be shared, confirm that the recipients’ Excel versions support the functions used. The Windows and Mac desktop editions of Microsoft 365 have the same core lookup functions; exact ribbon appearance can differ, but these formulas do not require a special menu command.

Frequently Asked Questions

What is the simplest Excel lookup formula?
For Microsoft 365 or Excel 2021 and later, XLOOKUP is often the simplest for an exact match. Give it the value to find, the search range, and the return range; optionally add a not-found message.

Why does VLOOKUP return the wrong column?
Its third argument counts from the first column of the selected table range, not from the worksheet’s column A. Check the table’s left edge and the column number, and use FALSE for an exact match.

Can Excel look up a value to the left?
Yes. XLOOKUP can search one range and return from a range on either side. INDEX and MATCH can also do this. VLOOKUP cannot return a column to the left of its search column.

Why does my lookup show #N/A?
Usually Excel did not find an exact match, or an approximate search has no eligible threshold. Check spelling, spaces, text-versus-number formatting, the selected range, and the match mode.

Can a lookup return more than one match?
A standard XLOOKUP returns one result, normally the first match. In Microsoft 365 or Excel 2021 and later, FILTER can return all rows meeting a condition, provided the output area is clear for the spilled results.

Will XLOOKUP work in every Excel version?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or earlier. Use INDEX and MATCH or VLOOKUP when compatibility with older releases is required.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.