This post is about the Lookup and Reference Functions in Microsoft Excel and how you can use these functions effectively.
Lookup and Reference Functions in Microsoft Excel
Lookup & Reference functions help you work with different data sets and are useful in cross-checking the data. Using these functions, you can get information about a range of data, the location of a given value, or look up for specific values.
Here let us check out some of the Lookup and Reference functions with examples:
VLOOKUP
The VLOOKUP (i.e, Vertical Lookup) function finds a value in the table’s leftmost column. It then displays the value in the same row from another column you specify.
- In the cell where you want to get the value, insert the VLOOKUP function.
=VLOOKUP(A2,$E$7:$G$11,3,FALSE)
the VLOOKUP function looks for the ID 104(A2) from the range ($E$7:$G$11) and returns the value from the third column(3). The argument FALSE is set to return an exact match or a #N/A error if not found.
- Drag the VLOOKUP function in cell B2 down to cell B5 so the cells below will be automatically updated.
HLOOKUP
The HLOOKUP (horizontal lookup) function searches for a value in the top row of a table and then returns a value in the same column from a row you specify in the table.
=HLOOKUP(A2,$E$6:$H$8,3,FALSE)
MATCH
The MATCH function returns the position of a value in a given range.
=MATCH(A2,$E$5:$I$5,0)
Here the fourth argument indicates:
- 0 -> exact equal
- 1 -> less than
- -1 -> greater than
INDEX
Uses an index to choose a value from an array.
=INDEX(G6:H10,A2,B2)
Here G6:H10 is the array to be looked upon, A2 and B2 are the row number and column number respectively.
CHOOSE
The CHOOSE function returns a value from a list of values, based on a position number.
A3 is the index number and the rest is the list of values the user gives.
