Home > Formulas > Lookup > Compare How to Use Excel Lookup FunctionsHow to pick the best lookup function in Microsoft Excel. Compare XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH, and OFFSET Authors: Debra Dalgleish, Alex Blakenburg 
XLOOKUP FunctionThe XLOOKUP function is a twoway lookup that will work either as a vertical or horizontal lookup. NOTE: XLOOKUP is only available in MS 365 or Excel 2021 or later.

ExampleIn the example shown below, the following Excel XLOOKUP function is in cell G2: =XLOOKUP($F$2, tblProductsXLK2[Code], tblProductsXLK2[Item], "Not Found")
In cell G2, the formula result is "Sweater", from worksheet row 5, where the code "SW001" was found. 
Advantages
Disadvantages
More InformationClick the link to read more about the XLOOKUP function, and see other examples, and get a download file. 
VLOOKUP FunctionThe VLOOKUP function is a Vertical lookup.
ExampleIn 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) Advantages
Disadvantages
More InformationClick the link to read more about the VLOOKUP function, and see other examples, and get a download file. 
HLOOKUP FunctionThe 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. ExampleIn 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) Advantages
Disadvantages
More InformationClick the link to read more about the HLOOKUP function, and see other examples, and get a download file. 
LOOKUP FunctionThe LOOKUP function has two syntax forms  Vector and Array.
ExampleThis 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). Advantages
Disadvantages
More InformationClick the link to read more about the LOOKUP functions, and see other examples, and get a download file. 
INDEX / MATCH FunctionsThe INDEX and MATCH functions can be combined to return a value from a range
ExampleIn 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)) Advantages
Disadvantages
More InformationClick the link to read more about the INDEX / MATCH functions, and see other examples, and get a download file. 
OFFSET FunctionThe OFFSET function returns a reference, of a specified size, offset from the starting reference. ExampleIn the following table, you could get the value from the cell that is down 4 rows from cell B3. =OFFSET(B3,C1,0) Advantages
Disadvantages
More InformationClick the link to read more about the OFFSET function, and see other examples, and get a download file 
Download the Sample FileDownload 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. More Functions 
Did Excel's lookup formulas ever make you want to cry? Don't worry  you're not alone!
Last updated: March 8, 2024 12:50 PM