Lookup & Reference Functions

VLookup    |   HLookup    |   Match    |   Index    |   Choose

Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function.

VLookup

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.

HLookup

In a similar way you can use the HLOOKUP (Horizontal lookup) function.

Match

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.

Index

The INDEX function returns the value at the intersection of a row (3) and column (2) index in a given range.

Choose

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.

Go to Top: Lookup & Reference Functions    |    Go to Next Topic: Financial Functions