Clarification of parameters in an INDEX MATCH formula

0
votes

I am working through a spreadsheet created by someone else and in one Worksheet Column A has a value obtained with the formula below;

=INDEX(Sites!A$2:AC$10000,MATCH(F2,Sites!P$2:P$10000,FALSE),3)

I am confused by this formula and wondered if someone could clarify it for me please. I have never used an INDEX MATCH formula before and when I google for it, there are no examples that use a FALSE or TRUE before the end value, in this case "3". Also if the last value is "3" or the third column in the lookup range - how can this be when the P$2:P$10000 only has one column, Column P?

Here is a screen image of the worksheet

enter image description here

2
excelexcel-formula
It is not one column, it is 29 columns wide, the 3 is referring to the index function not the match function. – Scott Craner

2 Answers

1
votes

The FALSE works but is wrong.

With the MATCH function the 3rd parameter is optional. If not specified it defaults to the value of 1, which tells the MATCH function that the data to search is sorted and to therefore use a Binary Search algorithm to do the matching... which is really fast.

A value of zero, tells the MATCH function to instead do a linear search from top to bottom until the value is found, or not found at the end. This is called an Exact-Match search. The value 0 should be used here. It just so happens that FALSE evaluates to zero, but there is no reason to use it here, and it is just misleading. It should be simply 0.

...

Regarding the 3 at the very end. This instructs the INDEX function to return the corresponding value in the THIRD column of: Sites!A$2:AC$10000.

0
votes

Index Match is a pretty powerful combination. It will return a value from an Index (which you set via a range), then uses a Match() to get the row.

In your example, the formula will return some value in the range A2:AC10000. [I think though this needs to be edited to just A2:A10000?]

Great, but which cell in that range? We need to know a row. Think of it like getting a cell in a cross section. The Index part is the column, and now you need a way to say what ROW to use. This is where Match() comes in.

Match first uses some value you want to find. In your example, it's looking at the building name ("Kilibarachan Primary"). This name exists in a column somewhere, Column P. It will find that factory name in column P, and return the row number. This row number is then fed to the Index. Now you have the column (A) and the intersection row.

Edit: Excel Hero beat me to this, but I figured I'd leave it anyways.