Lookup one value, Return matching correspnding values

J

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.
 
M

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
 
M

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
 
J

JJ

Mike

Thank a mil.

I tried it and it works.

JJ

Mike H said:
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
 
J

JJ

Hi Max

I tried it and yues, it is working.

Tx, appreciate your assistance.

JJ
South Africa
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top