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

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

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

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

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

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

Index Match Help
 

Thanks for the feedback

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"MrSales" wrote in
message ...

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