Leading the way in Microsoft Office Development

VLOOKUP Equivalent

If no match is found in the table column then the VLOOKUP() function will always return the next value "less than" the value you want.

If we used the VLOOKUP function to search for the value 12 then "10" would be returned, assuming the table was sorted in ascending order.

It is possible to return the next largest value instead of the next smallest value.

The VLOOKUP() function is basically a combination of 2 other functions, the MATCH() and the OFFSET().

We can use these functions to return the next largest value. The only condition being that the list must be sorted in descending order.

Without Heading Row

This example looks up the value "12" (in the first column).

Using a combination of the MATCH() and the OFFSET() functions it is possible to obtain the value "Thirteen" from this table.

The first cell reference is the cell you want to offset against, in this case "B2", which is the top of the list.

The first argument passed to the MATCH function is the value you want to search for, in this case 12.

The second argument passed to the MATCH function is a single column of cells containing the numbers to search, in this case "B2:B13".

The third argument passed to the MATCH function tells it that we want to find the next largest value if no match is found, in this case "-1".

The MATCH() function will return the position of a value in a list. In this example the number 6 will be returned because the next largest value greater than 12 is 13 which is in position 6.

The OFFSET() function returns the value from a cell which is an offset from another cell. In this example we are offsetting from the cell "B2".

We need to subtract one from the number returned from the MATCH function in order to get the correct offset.

The third argument passed to the OFFSET function tells it the number of columns to offset before returning the value. We want to return the entry from column "C", in this case 1.

The last two arguments passed to the OFFSET function tell it the height and width of the number of cells to return. We only want to return the value in a single cell so we pass the values 1.

With Heading Row

This example looks up the value "12" (in the first column).

Using a combination of the MATCH() and the OFFSET() functions it is possible to obtain the value "Thirteen" from this table.

The first cell reference is the cell you want to offset against, in this case "B2", which is the heading above the list.

The first argument passed to the MATCH function is the value you want to search for, in this case 12.

The second argument passed to the MATCH function is a single column of cells containing the numbers to search, in this case "B2:B13".

The third argument passed to the MATCH function tells it that we want to find the next largest value if no match is found, in this case "-1".

We do not need to subtract one from the number returned from the MATCH function because this will be the correct offset.

The third argument passed to the OFFSET function tells you the number of columns to offset before returning the value, in this case 1, as we want to return the entry from column "C".

These are a few more examples.