I mentioned in an earlier post using MATCH() and INDEX() as an alternative to OFFSET(). A comment to a post on the use of VLOOKUP() also suggested Match() and INDEX(), so it seems worth having a look at these two functions in a bit of detail.

In some ways MATCH() is similar to VLOOKUP(). It is designed to find a match for a particular value in a list of other values. Like VLOOKUP() there is an optional argument that can define the sort of match to look for – either an exact match or an approximate match if the list of values is sorted appropriately.

VLOOKUP() has two variants: if the optional argument is set to FALSE it will search for an exact match and the list does not need to be sorted; if the optional argument is set to TRUE or omitted VLOOKUP() will look for the largest value in the list that is less than or equal to the lookup value. The list must be sorted in ascending order.

MATCH() has three variants of its optional argument: 0 will search for an exact match and the list does not need to be sorted; 1 will look for the largest value in the list that is less than or equal to the lookup value. The list must be sorted in ascending order; -1 will look for the smallest value in the list that is greater than or equal to the lookup value and less than the previous item in the list. The list must be sorted in descending order

The other very significant difference between VLOOKUP() and MATCH() is that VLOOKUP() will return the actual value from a specified column in the lookup table from the same row as the matched item, whereas MATCH() just returns the position of the matched item in a one-dimensional list. So if the lookup value matches is the third item in the list MATCH() will return 3.

Hopefully an example may make this marginally more comprehensible:

The fact that MATCH() returns a position rather than a value, means that you will need to combine it with the INDEX() function to retrieve the value itself. INDEX() has two forms, we’ll just look at the simpler ‘array’ form for now. An array can be thought of as a group of individual values, so in this context a block of cells would be an array. INDEX() can use three arguments – the array, a row number and a column number. So having used MATCH() in cell D3 to find that the value we were after is the third item in the block of cells D7 to D11 we could find the value of this third item using:

=INDEX(D7:D11,D3,1)

The D7:D11 is the ‘array’ of cells, D3 returns the value of the MATCH() function in that cell: 3, and the ‘1’ indicates that we want to return the value from row 3 of column 1 (just as well, as our array only has one column!).

Because INDEX() can accept both a row and a column reference, it means we can use it to find an item at the intersection of a row and column in a two dimensional array. If this sounds a bit complicated, then you can always resort to the Lookup Wizard. Make sure that ‘Lookup Wizard’ is ticked in Tools, Add-ins. If it is then there should be a Lookup option in the Tools menu (Excel 2007 Formulas ribbon, Solutions section). Click on this to launch the Lookup Wizard which will guide you through creating a combined INDEX() and MATCH() formula. Notice that the MATCH() formula created just has a comma at the end rather than -1,0 or 1. This is shorthand for ,0 and is different from just omitting the final comma which has the same effect as entering ,1. Confused – you should be!