Lookup & Reference Functions
Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function.
The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify.
Explanation: the VLOOKUP function has 4 arguments. First, the lookup_value (A2). Second, the table_array (MyTable refers to the range E4:G7). By creating a named range we can easily copy the VLOOKUP function to the other cells. Third, col_index_num (3). This is the column number of the table_array from which the value should be returned. Fourth, range_lookup (FALSE). Set this argument to FALSE to return an exact match or a #N/A error if not found.
In a similar way you can use the HLOOKUP (Horizontal lookup) function.
The MATCH function returns the position of an item (Yellow) in a given range.
Note: the third argument is optional. Set this argument to 0 to return the position of the item that is exactly equal to lookup_value (A2) or to return a #N/A error.
The INDEX function returns the value at the intersection of a row (3) and column (2) index in a given range.
The CHOOSE function returns a value from a list of values, based on an index number (4).
Did you find this information helpful? Show your appreciation, vote for us.