Part 3 Index And Match

The last in a set of three looking this time at INDEX and MATCH as a way to search a table and return the contents of a cell. This combination is a more versatile and perhaps more robust method of looking up data in a table than VLOOKUP.

When I look at these two functions I almost always use them together. It looks somewhat complex but thinking through and breaking it down it becomes clear and there are significant advantages. First of all indexing is a method of pointing to something so if you imagine a table and you tell the programme which row to go to and which column to align to, you end up and an intersection and the contents of the cell are returned. This may have it s uses but it is rather inflexible on its own.

By introducing MATCH to replace a specified row or column number you are now making the function so much more flexible and powerful. The MATCH function looks at a range (either a column or row) and then sees whether the specified value has a match within that range. It then returns a number. Why is this so useful? Well unlike Vlookup, the lookup value does not need to be in the first column. Secondly you are using a specific match to specify the column number and therefore if the order of the table columns change, it will not impact the function (as long as the matching column has not been deleted that is). Let us look at each of these in turn and then combine them.

=INDEX(A1:N5600,1,1)

The =INDEX tells the programme what function you are using

All functions are enclosed in brackets ()

The A1:N5000 is the table array or range where the programme will look.

The 1 is specified row number in the array

The 1 is the specified column number in the array

=MATCH(B6,F1:F5600,0)

The =MATCH tells the programme what function you are using

All functions are enclosed in brackets ()

The B6 is the lookup value.

The F1:F5600 is range that the programme will search (in this case it will identify a row number as it is searching rows 1 to 5600 in column F)

The 0 is the specified that the match needs to be exact and not approximate (1), this is the same as the VLOOKUP

The next step is to combine both functions:

=INDEX(A1:N5600, MATCH(B6,F1:F5600,0), MATCH( Qty ,A1:N1,0))

So we have now adapted the formula to replace specified column and row numbers with flexible MATCH functions looking for order number (entered into B6) with Qty. It is critical that both the order number and exact spelling of Qty is matched in the table otherwise an error will be shown. You should use the IFERROR with these as well.

Need a demo or to learn the steps in a session? Feel free to get in touch!