Hi Brian ,
The reason your formula is giving a correct result in the first instance and wrong results thereafter is because of the way the INDEX formula is supposed to function.
Your formula in the first instance is :
=INDEX($BD$5:$BF$13,MATCH(AT6,$BD$5:BD$13,0),MATCH(AU6,$BE$5:$BE$13,0))
An INDEX formula has a range as its first parameter ; this range can be a single row multiple column range , or a single column multiple row range , or a range with multiple rows and multiple columns.
Examples of these 3 would be :
=INDEX($BD$6:$BF$6,1) --------- returns Algeria
=INDEX($BD$6:$BF$6,2) --------- returns Skikda
=INDEX($BD$6:$BF$6,3) --------- returns Port
=INDEX($BE$6:$BE$13,1) -------- returns Skikda
=INDEX($BE$6:$BE$13,2) -------- returns Buenos Aires
=INDEX($BE$6:$BE$13,3) -------- returns Adelaide
You can see that in both the above 2 cases , the INDEX function has used only 2 parameters ; the first one specifies the data range , and the second one specifies the index value which is to be used to retrieve the data item from that location within the range.
When you use the INDEX function with a multiple row multiple column data range , you need to specify 2 indices , one for the row and the other for the column ; this is exactly the same way as you specify a worksheet cell location , by giving both the column reference and the row reference e.g. J17 represents the intersection of the column J and the row 17.
In this form of the INDEX function , the first index specifies the row index , and the second specifies the column index. In your formula , the row index is derived using the function MATCH as follows :
MATCH(AT6,$BD$5:BD$13,0)
This uses the data item Argentina and looks it up in the range BD5: BD13 ; the return value of the MATCH function will be 3.
The column index is derived using the function MATCH as follows :
MATCH(AU6,$BE$5:$BE$13,0)
This uses the data item Buenos Aires and looks it up in the range BE5: BE13 ; the return value of the MATCH function will be 3.
The above INDEX formula is thus reducing to :
=INDEX($BD$5:$BF$13,3,3)
which is the intersection of the 3rd column and 3rd row within the data range i.e. cell BF7 ; this returns the result Port.
Why does this fail when we go to the next instance ?
Because , here the first index derivation , which is now :
MATCH(AT7,$BD$5:BD$13,0)
returns the correct value of 4.
The second index derivation , which is now :
MATCH(AU7,$BE$5:$BE$13,0)
returns the correct value of 7.
However , the point is that this value of 7 is being used as the column index , which means the data range should have at least 7 columns ; in reality the data range has just 3 columns , which is why the error value #REF! is returned.
The basic flaw is that the way the data has been structured , there is no column orientation , since all of the data is in multiple rows ; the first column contains the Country Name , while the second column contains the City Name , and the third column contains the City Category.
If you are sure that two different countries cannot have identically named cities , then you can afford to just ignore the Country Name and lookup the City Name to retrieve the City Category.
Thus , a simple :
=INDEX($BF$6:$BF$13,MATCH(AU6,$BE$6:$BE$13,0))
entered in AV6 and copied down will suffice.
Only if two different countries can have identically named cities do you need more complex formulae using the INDEX function.
Narayan
