Index/Match ... Repost from 05/10/07

[Guest]

Excel2003 ...

Col A ... Text format ... values alpha-numeric & may repeat
Col B ... Text format ... values alpha-numeric & may repeat
(note: Col B sorted in Ascending order)
(note: length of Col A & B Cell contents & # of repeats is random)

Col L ... I am using INDEX/MATCH to find value in Col B, return Value in Col
A.

Formula working fine except when value in Col B repeats ... Issue is ... My
INDEX/MATCH formula is returning 1st value found in Col A (Q5X) ... What I
need when a match is found in Col B ... is the value returned from Col A that
looks like a number. For the example below ... I wish Col A value of 123 to
be returned.

Col A ...... Col B
Q5X ....... 1A5637
C6Y ........ 1A5637
123 ........ 1A5637
X2Z ........ 1A5637

Thank you for the guidance ... Kha

 

[Dave Peterson]

Looks like a number???

=INDEX(Sheet2!A1:A10,
(MATCH(1,(ISNUMBER(-Sheet2!A1:A10)*(Sheet2!B1:B10=B1)),0)))

Is a number:

=INDEX(Sheet2!A1:A10,
(MATCH(1,(ISNUMBER(Sheet2!A1:A10)*(Sheet2!B1:B10=B1)),0)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

 

[Guest]

Mr Dave ...

Perfect ... Thank you for the Magic ... Kha