How to pick the best lookup function in Excel. Compare VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH, and OFFSET
The VLOOKUP function is a Vertical lookup.
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)
Click the link to read more about the VLOOKUP function, and see other examples, and get a download file.
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.
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)
Click the link to read more about the HLOOKUP function, and see other examples, and get a download file.
The LOOKUP function has two syntax forms -- Vector and Array.
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).
Click the link to read more about the LOOKUP functions, and see other examples, and get a download file.
The INDEX and MATCH functions can be combined to return a value from a range
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))
Click the link to read more about the INDEX / MATCH functions, and see other examples, and get a download file.
The OFFSET function returns a reference, of a specified size, offset from the starting reference.
In the following table, you could get the value from the cell that is down 4 rows from cell B3.
=OFFSET(B3,C1,0)
Click the link to read more about the OFFSET function, and see other examples, and get a download file
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.
Last updated: April 16, 2021 3:28 PM