Microsoft Excel Lookup Function Examples That Work

For most exact-match lookups in Microsoft Excel for Microsoft 365, use XLOOKUP: with product codes in A2:A6, prices in C2:C6, and the code to find in E2, enter =XLOOKUP(E2,A2:A6,C2:C6,"Not found"). If E2 contains P-103 and that row’s price is 12.5, the result is 12.5; the examples below show how XLOOKUP and other Excel lookup functions handle different kinds of searches.

What a lookup function does

A lookup formula searches a range for a value, then returns related information from another range. For example, you can enter an employee ID and return the employee’s department, or enter a product code and return its price. The search value is often called the lookup value; the range searched is the lookup array or lookup column; and the range supplying the answer is the return range.

Keep the lookup values and their corresponding results aligned. If A2 contains P-101 and C2 contains that product’s price, the formula should search column A and return from column C. Sorting matters for approximate lookups, but ordinarily not for exact-match XLOOKUP, VLOOKUP with FALSE, or MATCH with 0.

In Microsoft 365 for Windows, type a formula in a worksheet cell and press Enter. Excel’s formula separator is usually a comma in English-language settings; some regional settings use semicolons instead. The examples use commas and standard cell references.

XLOOKUP: the flexible option for exact matches

XLOOKUP is usually the clearest starting point in current Excel. It searches one range and returns a value from a corresponding position in another range. Unlike VLOOKUP, its return range can be to the left or right of the search range, and it does not require a column number.

With product codes in A2:A6, product names in B2:B6, prices in C2:C6, and the requested code in E2, use =XLOOKUP(E2,A2:A6,C2:C6,"Not found"). If E2 is P-103 and the matching price in C4 is 12.5, the result is 12.5. The fourth argument supplies a readable result when no match exists.

To return a product name instead, keep the same layout and use =XLOOKUP(E2,A2:A6,B2:B6,"Not found"). If P-103 is listed as Desk lamp in B4, the result is Desk lamp. By default, XLOOKUP looks for an exact match, which is generally appropriate for IDs, names, and codes.

XLOOKUP can also return more than one column. With product codes in A2:A6, names and prices in B2:C6, and the code in E2, use =XLOOKUP(E2,A2:A6,B2:C6,"Not found"). For P-103, Excel returns the corresponding name and price across two cells, provided the cells to the right are clear for the result to spill.

For duplicate values, XLOOKUP normally returns the first match it encounters. To search from the bottom upward and return the last matching record, with IDs in A2:A20, dates in B2:B20, values in C2:C20, and the ID to find in E2, use =XLOOKUP(E2,A2:A20,C2:C20,"Not found",0,-1). If the same ID appears in rows 4 and 15, this searches from row 20 upward and returns the value from row 15.

VLOOKUP: a familiar choice for older workbooks

VLOOKUP searches down the first column of a table and returns a value from a column to its right. It remains useful when a workbook must work with older Excel versions that do not include XLOOKUP. Its main limitation is that it cannot return a value from a column to the left of the search column.

With product codes in A2:A6, names in B2:B6, prices in C2:C6, and the code in E2, enter =VLOOKUP(E2,A2:C6,3,FALSE). If E2 contains P-103 and the price in the third column of the table is 12.5, Excel returns 12.5. The 3 means to return the third column of A2:C6, and FALSE requests an exact match.

Use FALSE explicitly for codes, names, and other values that should match exactly. If you omit the fourth argument, VLOOKUP uses approximate matching, which can return an unexpected result when the first column is not sorted as required. A changed table layout can also make the hard-coded column number point to the wrong field.

For example, if the lookup value is in column C and the answer is in column A, VLOOKUP cannot search left within the table. Use XLOOKUP, or use INDEX and MATCH instead. Inserting a column within a VLOOKUP table can also affect which column number returns the desired answer, so check formulas after changing a table’s structure.

LOOKUP: approximate matches against sorted values

LOOKUP is an older function that is most useful for finding a value in a sorted one-row or one-column range and returning the corresponding item from another range. It performs an approximate search: if it cannot find an exact value, it returns the result associated with the largest lookup value that is less than or equal to the value being searched for. The lookup vector must be sorted in ascending order for dependable results.

Suppose minimum score thresholds are in A2:A5 as 0, 60, 70, and 80, and their grade labels are in B2:B5 as F, D, C, and B. With a score in E2, use =LOOKUP(E2,A2:A5,B2:B5). If E2 is 87, Excel finds the largest threshold not greater than 87, which is 80, and returns B. If the threshold values are not in ascending order, LOOKUP may return an incorrect grade.

LOOKUP is not the best choice for a unique exact search when the data is unsorted. In that situation, XLOOKUP with its default exact-match behavior is easier to interpret. For a range-based task such as assigning a rate or category by threshold, LOOKUP can be concise, but document the sorted thresholds and test boundary values.

INDEX and MATCH: a flexible alternative

INDEX and MATCH work together: MATCH finds the position of a value, and INDEX returns the item at that position in a separate range. This combination can search in one column and return from either side, making it a common alternative in workbooks built before XLOOKUP became available.

With product codes in A2:A6, prices in C2:C6, and the requested code in E2, use =INDEX(C2:C6,MATCH(E2,A2:A6,0)). If P-103 is the third item in A2:A6 and its price in C4 is 12.5, MATCH returns position 3 and INDEX returns 12.5. The 0 tells MATCH to find an exact match.

INDEX and MATCH can also search to the left. With names in A2:A6, IDs in B2:B6, and an ID in E2, use =INDEX(A2:A6,MATCH(E2,B2:B6,0)). If the ID in E2 is 204 and it is on the same row as Morgan Lee, the formula returns Morgan Lee. This avoids VLOOKUP’s left-to-right restriction.

One practical difference is readability. XLOOKUP expresses the search range and return range directly in a single function, while INDEX/MATCH separates the position-finding and result-returning steps. Both can be useful; when sharing files, consider whether the people opening the workbook use an Excel version that supports the functions in it.

HLOOKUP: searching across a row

HLOOKUP is the horizontal counterpart to VLOOKUP. It searches across the first row of a table and returns a value from a specified row beneath the match. It can help with older worksheets arranged with months, categories, or other headings across columns, though a vertical list is often easier to expand and manage.

Suppose month names are in B1:E1, sales figures are in B2:E2, and the month to find is in G1. Use =HLOOKUP(G1,B1:E2,2,FALSE). If G1 contains Mar and the value in D2 is 4200, the result is 4200. The 2 selects the second row of the table, and FALSE requires an exact heading match.

Choosing the right lookup formula

Pick the function based on the layout, match rule, and Excel versions that need to open the file. These practical guidelines cover most worksheet lookups:

Choose XLOOKUP for a new exact-match lookup when the workbook’s Excel versions support it. It is flexible about return direction and can display a custom not-found message.
Choose VLOOKUP when the search field is the leftmost column, the return field is to its right, and compatibility with older Excel files matters.
Choose INDEX and MATCH when you need a lookup that can return from either side and want a pattern supported in older versions than XLOOKUP.
Choose LOOKUP for a deliberate approximate threshold search with a lookup vector sorted in ascending order.
Choose HLOOKUP when the lookup headings run across the first row of a horizontal table.

For bracketed categories, test values at the exact thresholds as well as just above and below them. For exact ID searches, include values that are absent from the lookup list. These checks help reveal incorrect ranges, unexpected duplicates, and a mistaken exact-versus-approximate match setting.

Common lookup errors and how to fix them

#N/A commonly means that an exact match was not found. Check for extra spaces, inconsistent spelling, and values stored as text in one range but as numbers in another. Also verify that the lookup range includes the row containing the expected value. XLOOKUP can show a custom message through its fourth argument; with the product layout used earlier, =XLOOKUP(E2,A2:A6,C2:C6,"Check product code") returns Check product code when no exact match is found.

#REF! can indicate that a referenced cell or range was deleted or that a formula refers to an invalid return range. In VLOOKUP, an incorrect column index can also cause a reference error if it exceeds the number of columns in the table array. Confirm that the table range and return-column number still match the worksheet.

A surprising result instead of an error often points to approximate matching or unsorted threshold data. In VLOOKUP, check that the fourth argument is FALSE when an exact match is needed. With LOOKUP, check that the lookup vector is in ascending order and that approximate matching is actually intended.

Numbers that look alike but do not match may be stored differently. For instance, a code such as 00125 can be treated as text in one location and as a number in another, or leading zeros may have been lost. Decide whether the code is text or numeric and make the source and search values consistent; for identifiers, text is often appropriate when leading zeros are meaningful.

A returned zero for a blank cell can be confusing: a lookup may find the correct row even though the corresponding return cell is empty. Do not assume that a zero always means the lookup failed. Check the source cell and decide whether a blank, a zero, or a message is the intended result.

Excel version support and worksheet habits

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported later releases. It is not available in some older perpetual Excel versions, so a recipient using an older release may see a function-name error. VLOOKUP, HLOOKUP, LOOKUP, INDEX, and MATCH are established functions available in older Excel releases as well.

Excel for Mac and Excel for the web also support many modern lookup functions when the account or installed version includes them; the exact availability can depend on the release. The formulas here use the same basic syntax in current Windows, Mac, and web editions. If a workbook is shared with users on older desktop Excel, compatibility is a workbook-planning question, not simply a matter of whether the formula works on the creator’s computer.

For maintainable workbooks, use clear headings, keep related lookup and return ranges the same height, and avoid including unrelated columns in a table array. If the lookup data grows, converting it into an Excel table can make the source data easier to manage. Always test a normal match, a missing value, and any relevant boundary case before relying on the returned results.

Frequently Asked Questions

What is the easiest lookup formula in Excel?

For an exact match in a current Excel version, XLOOKUP is usually the most straightforward choice because it takes separate lookup and return ranges and can include a not-found message. For an older Excel version without XLOOKUP, VLOOKUP with FALSE or INDEX and MATCH are common alternatives.

Is XLOOKUP better than VLOOKUP?

XLOOKUP is more flexible: it can return values from either side of the search range, defaults to an exact match, and can provide a custom not-found result. VLOOKUP remains useful for compatibility with older Excel releases and for existing workbooks, but it searches only in the table’s first column and returns to the right.

Why does my Excel lookup return #N/A?

The lookup value may not exactly match a value in the search range, or the formula may search the wrong cells. Check for extra spaces, different data types, missing rows, and differences such as leading zeros. If you are using an exact lookup, confirm that the value truly appears in the selected range.

Does LOOKUP require sorted data?

Yes. For the common vector form of LOOKUP, the lookup vector should be sorted in ascending order. LOOKUP uses approximate matching and returns the result associated with the largest lookup value that does not exceed the search value, rather than requiring an exact match.

Can Excel look up a value to the left?

Yes. XLOOKUP can return from a range to the left of its lookup range. INDEX and MATCH can do so as well. VLOOKUP cannot return a column to the left of the first column in its table array, so use one of the other methods when the worksheet is arranged that way.

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.