Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and index returning N/A
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and index returning N/A
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and index returning N/A
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" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and index returning N/A
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 -- "DianeG" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and index returning N/A
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)) -- Regards Roger Govier "DianeG" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and index returning N/A
Thank you both very much, I'm going to try and stick my hair back on now!!
Regards Diane "Roger Govier" wrote: 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)) -- Regards Roger Govier "DianeG" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and index returning N/A
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX/MATCH/LARGE returning #VALUE! error | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
INDEX - MATCH - VLOOKUP - returning missing reference | Excel Discussion (Misc queries) | |||
Returning MULTIPLE values with Index and Match | Excel Discussion (Misc queries) | |||
Match/Index Returning #N/A | Excel Worksheet Functions |