Match and index returning N/A

[DianeG]

Please help with this I'm tearing my hair out. My table looks like this:

Gin Vodka Brandy Whisky
North 456 654 776 776
South 1000 764 965 464
East 532 642 642 1234
West 357 456 852 842


Gin starts in B1. I want to find out say, the Brandy sales for the East
using Index and match using the formula as follows:
The text in F6 is Brandy and in F7 East
=INDEX($A$1:$E$5,MATCH(F6,$A$1:$E$1,),MATCH(F7,$A$1:$A$5,))
This works OK for brandy and East but when I changed East to West I got 1234
then for Vodka for West I got 464. I can't understand what's going wrong, it
works for other tables.

Thanks in advance

Diane

 

[PCLIVE]

I think you match functions are reversed.

Try this:

=INDEX($A$1:$E$5,MATCH(F7,$A$1:$A$5,0),MATCH(F6,$A$1:$E$1,0))

Regards,
Paul

 

[DianeG]

Thanks SO much, I can't see why that matters though after all we're just
cross referencing. Do you know if there any rules that you have to follow
then?

Regards

Diane

 

[PCLIVE]

The INDEX function has you specify a row number first, and then a column
number.

Your first MATCH function is this: MATCH(F6,$A$1:$E$1,),
F6 is the criteria in which you are determining a column. Remember, row
first, then column.

And your second MATCH funtion was: MATCH(F7,$A$1:$A$5,))
For this one you're looking up F7, which is from the items in column 1 that
trying to determine the appropriate row.

So really, you just needed to swap those two so that you find the row first
(F7) and the column second (F6).

The reason it seemed to work for "Brandy" and "East" is because both match
functions returned the same number, "4". That is, row 4 and column 4.


Hope this helps,
Paul

--

 

[Roger Govier]

Hi Diane

Index wants the Row value first, followed by the Column Value.
You have your formula the other way around.
Change to
=INDEX($A$1:$E$5,MATCH(F7,$A$1:$A$5,0),MATCH(F6,$A$1:$E$1,0))

 

[DianeG]

Thank you both very much, I'm going to try and stick my hair back on now!!

Regards

Diane

 

[Dave Curtis]

Hi,

If you're using Excel 2003, (Don't know whether it still works in 2007), you
can go into Tools/Options/Calculation and check the "Accept labels in
formulas" box.

Then you can use the so-called "natural language" feature, and simply enter,
for instance,

=Gin North

in a cell and get the result of 456.

Dave

url:http://www.ureader.com/msg/104242325.aspx