SPACEEXCEL

Lookup & Reference Functions

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.

1. Insert the VLOOKUP function shown below.

Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not found.

2. Drag the VLOOKUP function in cell B2 down to cell B11.

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the same, while the relative reference (A2) changes to A3, A4, A5, etc.

HLookup

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

Match

The MATCH function returns the position of a value in a given range.

Note: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set this argument to 0 to return the position of the value that is exactly equal to lookup_value (A2) or a #N/A error if not found.

Index

The INDEX function returns a specific value in a two-dimensional or one-dimensional range.

Note: 92 found at the intersection of row 3 and column 2 in the range E4:F7.

Note: 97 found at position 3 in the range E4:E7.

Choose

The CHOOSE function returns a value from a list of values, based on a position number.

Tax Rates

Sometimes you are not looking for an exact match when you use the VLOOKUP function in Excel. For example, when you want to calculate the tax on an income.

The following tax rates apply to individuals who are residents of Australia.

Taxable income	Tax on this income
0 - $6,000	Nil
$6,001 - $35,000	15c for each $1 over $6,000
$35,001 - $80,000	$4,350 plus 30c for each $1 over $35,000
$80,001 - $180,000	$17,850 plus 38c for each $1 over $80,000
$180,001 and over	$55,850 plus 45c for each $1 over $180,000

Example: if income is 37000, tax equals 4350 + 0.3 * (37000 - 35000) = 4350 + 600 = $4950

To automatically calculate the tax on an income, execute the following steps.

1. On the second sheet, create the following range and name it Rates.

2. We already know how the VLOOKUP function can return an exact match or a #N/A error if not found, by setting the fourth argument to FALSE. However, when you set this argument to TRUE, it returns an exact match or if not found, it returns the largest value smaller than lookup_value (A2). That's exactly what we want!

Explanation: Excel cannot find 37000 in the first column of Rates. However, it can find 35000 (the largest value smaller than 37000). As a result, it returns 4350 (col_index_num, the third argument, is set to 2).

3. Now, what's left is the remainder of the equation, + 0.3 * (37000 - 35000). This is easy. We can return 0.3 by setting col_index_num to 3 and return 35000 by setting col_index_num to 1. The complete formula below does the trick.

Note: when you set the fourth argument of the VLOOKUP function to TRUE, the first column of the table must be sorted in ascending order.

Offset

The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.

1. The OFFSET function below returns the cell that is 3 rows below and 2 columns to the right of cell A2. The OFFSET function returns a cell because the height and width are both set to 1.

Result:

2. The OFFSET function below returns the 1 x 2 range that is 5 rows below and 1 column to the right of cell A2. The SUM function calculates the sum of this range.

Result:

Note: to return a range (without calculating the sum), select a range of the same size before you insert the OFFSET function. If you want to return a cell or range of cells that is a specified number of rows above or columns to the left, enter a negative number.

Left Lookup

A drawback of the VLOOKUP function is that it can only look up values in the leftmost column of a table. However, sometimes you need to look up a value in any column and return the corresponding value to the left. To achieve this, simply use the INDEX and the MATCH function.

1. The MATCH function returns the position of a value in a given range.

Explanation: 104 found at position 4 in the range $G$4:$G$7.

2. Use this result and the INDEX function to return the 4th value in the range $E$4:$E$7.

3. Drag the formula in cell B2 down to cell B11.

Note: when we drag this formula down, the absolute references ($E$4:$E$7 and $G$4:$G$7) stay the same, while the relative reference (A2) changes to A3, A4, A5, etc.

Two-way Lookup

This example teaches you how to lookup a value in a two-dimensional range. We use the MATCH and INDEX function.

Below you can find the sales of different ice cream flavors in each month.

1. To find the row number of Feb, use the MATCH function.

2. To find the column number of Chocolate, use the MATCH function.

3. To find the sales of Chocolate in February, use the INDEX function.

4. Put it all together.

Locate Maximum Value

This example teaches you how to find the cell address of the maximum value in a column.

1. First, we use the MAX function to find the maximum value in column A.

2. Second, we use the MATCH function to find the row number of the maximum value.

Explanation: the MATCH function reduces to =MATCH(12,A:A,0), 7. The MATCH function returns the position of the maximum value in column A. Set the third argument to 0 to return an exact match.

3. Finally, we use the ADDRESS function to return the cell address.

Explanation: the ADDRESS function reduces to =ADDRESS(7,1), $A$7. The first argument specifies the row number. The second argument specifies the column number.

Indirect

The INDIRECT function in Excel returns the reference specified by a text string.

1. For example, the INDIRECT function below reduces to =INDIRECT("C2"), =C2, 5