first argument is lookup value - what value to look for (code entered in cell F2)
second argument is the lookup range - where to look for the code (Code column)
third argument is the return range - where to find the item name (Item column)
fourth argument (optional) is the result to return if valid match is not found
5th and 6th arguments (optional) were not used, so the default settings for those arguments were used.

In cell G2, the formula result is "Sweater", from worksheet row 5, where the code "SW001" was found.

vlookup value left column

Advantages

Very flexible -- use XLOOKUP to find lookup value in any lookup range, row or column.
Match modes can specifiy an exact match or approximate match type
Search mode can find first item or last item (reverse search) that matches
Binary search mode for lists sorted in ascending or descending order
By using 2 XLOOKUP functions can find both the row and the column
Can return multiple cells using SPILL
Can use multiple criteria
Combine * (asterisk) or ? (question mark) with ~ (tilde) to find as a character, or use them as a wildcard match for partial matches.
Result is a cell or range, so there is the option to use the address in another formula such as SUM, instead of simply returning the value from the cell(s).
These are not volatile functions, so they have less impact on calculations in a large workbook

Disadvantages

XLOOKUP formula only available in MS 365 or Excel 2021 or later version of Excel
Using 2 XLOOKUP functions in a single formula, for finding row and column is less intuitive than the similar INDEX-MATCH-MATCH function

More Information

Click the link to read more about the XLOOKUP function, and see other examples, and get a download file.

VLOOKUP Function

The VLOOKUP function is a Vertical lookup.

It looks down the left column of a range, to find a value.
Then, it returns a value from another column in the range, from the row where the matching value was found.

Example

In the following table, you could look for "East", and get the January sales, which is in the 2nd column of the range B4:E7.

=VLOOKUP(C1,B4:E7,2,0)

vlookup value left column

Advantages

Simple to use, once you understand how it works.
Can find an exact or approximate match

Disadvantages

It is a volatile function, and can slow down the calculations in a large workbook
The value that you're searching for must be in the leftmost column of the range.

More Information

Click the link to read more about the VLOOKUP function, and see other examples, and get a download file.

HLOOKUP Function

The HLOOKUP function looks across the top row of a range, to find a value. Then, it returns a value from another row in the range, from the column where the matching value was found.

Example

In the following table, you could look for "Jan", and get the West sales, which is in the 5th row of the range C3:E7.

=HLOOKUP(C1,C3:E7,5,0)

hlookup value top row

Advantages

Simple to use, once you understand how it works.
Can find an exact or approximate match

Disadvantages

It is a volatile function, and can slow down the calculations in a large workbook
The value that you're searching for must be in the top row of the range.

More Information

Click the link to read more about the HLOOKUP function, and see other examples, and get a download file.

LOOKUP Function

The LOOKUP function has two syntax forms -- Vector and Array.

With Vector form, it looks for a value in a specified column or row
With Array form, it looks in the first row or column of an array, and returns the matching value from the last row or column.

Example

This example uses LOOKUP in its Array form, with shift start times in column D, and shift numbers in column E.

When a factory machine breaks down, the incident start time is intered in column A, and this formula, in column B, calculates the shift number.

=LOOKUP(A4,$D$4:$E$7)

The formula finds an approximate match for the start time in the first column of the lookup table (D), and returns a value from that row, in the last column of the lookup table (E).

LOOKUP function

Advantages

Simple to use
Useful within other formulas
Fast

Disadvantages

Approximate match only
Lookup array or vector must be sorted in ascending order.

More Information

Click the link to read more about the LOOKUP functions, and see other examples, and get a download file.

INDEX / MATCH Functions

The INDEX and MATCH functions can be combined to return a value from a range

The INDEX function can return an item from a specific position in a list.
The MATCH function can return the position of a value in a list
MATCH can be used to find both the row and column position

Example

In the following table, the MATCH function finds the row for East, and the column for Mar, and the INDEX function returns the value at that position.

=INDEX(B3:E7,MATCH(C1,B3:B7,0),MATCH(D1,B3:E3,0))

index match flexible lookup

Advantages

Very flexible -- look for value in any row or column.
Can find an exact or approximate match
These are not volatile functions, so they have less impact on calculations in a large workbook

Disadvantages

Two functions required, and more difficult to understand and set up .

More Information

Click the link to read more about the INDEX / MATCH functions, and see other examples, and get a download file.

OFFSET Function

The OFFSET function returns a reference, of a specified size, offset from the starting reference.

Example

In the following table, you could get the value from the cell that is down 4 rows from cell B3.

=OFFSET(B3,C1,0)

offset specific rows and columns

Advantages

Simple to use, once you understand how it works.
Can return a range of more than one cell

Disadvantages

It is a volatile function, and can slow down the calculations in a large workbook

More Information

Click the link to read more about the OFFSET function, and see other examples, and get a download file

Get the Excel Files ✅

Download the Excel Lookup Functions workbook, to see all the examples from this page. The zipped file is in xlsx format, and does not contain macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

Excel Lookup Function Humour

Did Excel's lookup formulas ever make you want to cry? Don't worry -- you're not alone!

My Excel tears are 30% lookup formulas

More Lookup Function Pages

Here are the links to more Excel lookup function pages.

VLOOKUP -- Excel VLOOKUP formula examples. Find product price. Change letter grades to numbers. Troubleshoot. Fix sorting problem. Videos, written steps, Excel file. Details on the Excel VLOOKUP Function Examples and Troubleshoot P page.

Multiple Criteria -- How to do Excel multiple criteria lookup with FILTER function or INDEX/MATCH formula. Which one to use? Free Excel file, step-by-step video, written steps. Details on the Excel Multiple Criteria Lookup INDEX MATCH or FILT page.

INDEX MATCH -- How to use Excel INDEX and MATCH functions to find data in list. Examples, videos, free workbooks. MATCH error troubleshooting steps. Details on the Excel Index Function Match Function Examples Troub page.

PIVOTBY -- Use Excel PIVOTBY function to quickly summarize data in layout like pivot table. Examples for all required and optional arguments. Interactive workbook. Details on the Excel PIVOTBY Function Examples-Basic-Advanced-Tes page.

INDIRECT -- Master Excel INDIRECT function with 4 practical examples. Create dynamic references, dependent drop-down lists. Videos, written steps, Excel workbook. Details on the Excel INDIRECT Function Ultimate Beginner Guide wi page.

Hyperlink -- Quick trick for Excel hyperlinks, easy HYPERLINK function examples. Hyperlink table of contents, or remove all hyperlinks. Excel hyperlink security warning. Details on the Excel Hyperlinks and Hyperlink Function Step-by-St page.

Bingo Cards -- Print Bingo Cards in Excel with numbers or pictures. Bingo number selector file shows used number on bingo board. Get the Vision Board Bingo template too!. Details on the Print Bingo Cards in Excel-Numbers-Pictures-Vision page.

XLOOKUP -- Use Microsoft Excel XLOOKUP function for vertical or horizontal search in lookup table. Like VLOOKUP and HLOOKUP combined, but better. Examples, practice file. Details on the Excel XLOOKUP Function Examples page.

Cost -- Enter current costs and planned spending cuts. Formulas calculate annual totals and differences. Details on the Excel Annual Cost Calculator Shows Totals and Diff page.

FILTER -- Simple Excel FILTER function examples. How to create lists that update automatically. Set rules, format list items. Screen shots, written steps, Excel file. Details on the Excel FILTER Function Examples - Lists Update Auto page.

HLOOKUP -- Get started with Excel HLOOKUP function, simple examples, how to fix problems. Video, free workbooks, easy steps. Understand errors, how HLOOKUP works. Details on the How to Use Excel HLOOKUP Function and Fix Problems page.

PMT -- Simple examples show how to calculate loan payment amounts with Excel PMT function. Step-by-step video, sample workbooks. Adjust for Canadian mortgage. Details on the How to Use Excel PMT Function for Loan Payment Amo page.

FILTER -- Excel FILTER function summary report examples show how to create multi-column reports, auto update if source data changes. Step-by-step notes, Excel file. Details on the How to Make Excel FILTER Function Summary Report T page.

Compare -- How to pick best Excel lookup function. Compare XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH, OFFSET. Function examples, pros and cons. Details on the Which Excel Lookup Function Is Best - Examples Pro page.

OFFSET -- Examples show how to use Excel OFFSET function. Video shows the steps and get the free workbook to follow along with the video. Details on the Excel OFFSET Function Examples with Video and Work page.

Calories -- Get this free Excel calorie burn calculator workbook. Select weight range, food item and activity, and see how long it takes to burn those calorie. Details on the Excel Calorie Burn Calculator Food Activity Free W page.

LOOKUP -- Excel LOOKUP function examples show how to get value from one-row or one-column range, or from array, with multiple rows and columns. Details on the Excel LOOKUP Function Examples with Video and Work page.

CHOOSE -- Excel CHOOSE function examples show how to select from a list of options, based on a number. Returns a value or reference. Details on the Excel CHOOSE Function Examples page.

30 Functions -- Learn 30 Excel functions in 30 days, by following this free challenge on the Contextures blog. Details on the 30 Excel Functions in 30 Days 30XL30D page.

Filter -- Interactive dynamic filter for Excel list. Drop down lists to pick criteria & column headings for filter results. Change selections any time, no macros. Details on the Interactive Excel Filter Choose Columns and Criter page.

VLOOKUP -- Step-by-step videos for Excel VLOOKUP function. Find product price in lookup table. Change student score to letter grade. Details on the How to Use Excel VLOOKUP Function Videos page.

Matching Items -- Pick region from drop down list. Excel table lists employees from that region. INDEX/MATCH formula, no macros. Free workbook, easy steps, screen shots. Details on the List Matching Items for Selection page.

FIND Function -- Use Excel FIND function to find location of case-sensitive text string, within other text. Three examples show how to use FIND.. Details on the Excel FIND Function 3 Examples Case-Sensitive page.

Transpose -- Excel TRANSPOSE function examples show how to flip data from vertical range to horizontal, or vice versa. Short video, written steps, and sample file for free download. Details on the Excel TRANSPOSE Function Examples with Video and W page.

Excel Topics