Lookup one value, Return matching correspnding values

[JJ]

I need to lookup a value in a column to return multiple corresponding values
from another worksheet.
Worksheet A: Worksheet B

Column A Column A Column B

ABC ABC 10
PLC 15
ABC 8

I want all the details for ABC.

 

[Mike H]

Hi,

This ARRAY formula looks up A1 in the active sheet for values on sheet 2.
Enter as an array *See Below* and then drag down to return the second and
third match etc. It will give a NUM error when it runs out of matches

=INDEX(Sheet2!$B$1:$B$13,LARGE((Sheet2!$A$1:$A$13=$A$1)*ROW($A$1:$A$13),COUNTIF(Sheet2!$A$1:$A$13,$A$1)+1-ROW(A1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

 

[Max]

A simple non array formulas play to extract it ...
Your source data as posted in sheet: B, cols A and B, data from row2 down
In sheet: A,
The input for the name will be in A2, eg: ABC
In B2: =IF(B!A2="","",IF(B!A2=A$2,ROW(),""))
In C2: =IF(ROWS($1:1)>COUNT(B:B),"",INDEX(B!B:B,SMALL(B:B,ROWS($1:1))))
Copy B2:C2 down to cover the max expected extent of source data in B, eg
down to C200? Minimize/hide away col B, Col C returns the expected results
for the input in A2, all neatly bunched at the top. Success? hit the YES below

 

[JJ]

Mike

Thank a mil.

I tried it and it works.

JJ

Hi,

This ARRAY formula looks up A1 in the active sheet for values on sheet 2.
Enter as an array *See Below* and then drag down to return the second and
third match etc. It will give a NUM error when it runs out of matches

=INDEX(Sheet2!$B$1:$B$13,LARGE((Sheet2!$A$1:$A$13=$A$1)*ROW($A$1:$A$13),COUNTIF(Sheet2!$A$1:$A$13,$A$1)+1-ROW(A1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

 

[JJ]

Max

Tx for the response. I will try it and let you know.

JJ

South Africa

 

[JJ]

Hi Max

I tried it and yues, it is working.

Tx, appreciate your assistance.

JJ
South Africa

 

[Max]

Welcome, and thanks for taking the moment to feedback

 

[Ashish Mathur]

Hi,

You may refer to my article abelow

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com