Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match Help
I'm having a real difficult time with this andi 'm sure it's no problem for you experts. I'm trying to find a match in columns A,B,C,D,E, or F and have the function return what's in column A in each instance. But the match could be in any of the columns every time I enter a new number. Here is my formula. =INDEX($A$14:$A$50000,MATCH(B4,$B$14:$F$50000,0)) A B C D B4 Formula MS21083D9 AN364D918 AN364D918A MS20364D918 MS21083N02 F22NTM-256 F22NTM-02 MS21083N04 F22NTM-440 F22NTM-40 MS20364-440A MS20364-440 AN364-440A AN364-440 Thank You -- MrSales ------------------------------------------------------------------------ MrSales's Profile: http://www.excelforum.com/member.php...o&userid=32572 View this thread: http://www.excelforum.com/showthread...hreadid=525492 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match Help
Here is one that will work
=INDEX($A$14:$A$50000,MAX((B$14:$F$50000=B4)*(ROW( B$14:$F$50000)))-ROWS($A$1:$A$14)+1) entered with ctrl + shift & enter Having said that, are you really using a range that is this large (A14:F50000)? If so I would not use that formula since an array formula has a tendency to be very slow if used on a large area. -- Regards, Peo Sjoblom "MrSales" wrote in message ... I'm having a real difficult time with this andi 'm sure it's no problem for you experts. I'm trying to find a match in columns A,B,C,D,E, or F and have the function return what's in column A in each instance. But the match could be in any of the columns every time I enter a new number. Here is my formula. =INDEX($A$14:$A$50000,MATCH(B4,$B$14:$F$50000,0)) A B C D B4 Formula MS21083D9 AN364D918 AN364D918A MS20364D918 MS21083N02 F22NTM-256 F22NTM-02 MS21083N04 F22NTM-440 F22NTM-40 MS20364-440A MS20364-440 AN364-440A AN364-440 Thank You -- MrSales ------------------------------------------------------------------------ MrSales's Profile: http://www.excelforum.com/member.php...o&userid=32572 View this thread: http://www.excelforum.com/showthread...hreadid=525492 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match Help
Do you have any other suggestions? I really appreciate your help Thank You -- MrSales ------------------------------------------------------------------------ MrSales's Profile: http://www.excelforum.com/member.php...o&userid=32572 View this thread: http://www.excelforum.com/showthread...hreadid=525492 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match Help
The formula you suggested is returning me an answer from column A but 12 lines below the answer from the same line that I originally wanted. Maybe I did not convey exactly what I need properly. I want the information in column A when I find the match in column A,B,C,D,E or F. Thanks for the help... -- MrSales ------------------------------------------------------------------------ MrSales's Profile: http://www.excelforum.com/member.php...o&userid=32572 View this thread: http://www.excelforum.com/showthread...hreadid=525492 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match Help
You have to adapt it and offset the rows from row 1 compared to where the
data start, if you don't offset it you need to let the index part start in the first row, i.e. =INDEX($A$1:$A$50000,MAX((B$14:$F$50000=B4)*(ROW(B $14:$F$50000)))) and enter with ctrl + shift & enter -- Regards, Peo Sjoblom "MrSales" wrote in message ... The formula you suggested is returning me an answer from column A but 12 lines below the answer from the same line that I originally wanted. Maybe I did not convey exactly what I need properly. I want the information in column A when I find the match in column A,B,C,D,E or F. Thanks for the help... -- MrSales ------------------------------------------------------------------------ MrSales's Profile: http://www.excelforum.com/member.php...o&userid=32572 View this thread: http://www.excelforum.com/showthread...hreadid=525492 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index Match Help
Thanks for the help Peo!!!! Works like a charm. -- MrSales ------------------------------------------------------------------------ MrSales's Profile: http://www.excelforum.com/member.php...o&userid=32572 View this thread: http://www.excelforum.com/showthread...hreadid=525492 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |