### NKW6 Pt 3 INDEX and MATCH

## 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.

## Comments

## Post a Comment