MrExcel Publishing
Your One Stop for Excel Tips & Solutions

vlookup question


Posted by Wolf Girl on June 25, 2001 8:59 AM

I am trying to extract values from one table and display them in another table.

Ex.
Code Name
D Madison
D Johnson
F Wilson
F Smith
F Jackson
S Wilder
S Adams

I want to pull all instances with a code of 'F'. Sometimes no instances, sometimes 10 instances. I tried vlookup and can only pull the first instance. Could someone pleeeeease help?

I saw something similar to this posted on the 6/16 but there was no answer.

Thanks in advance.


Posted by Aladin Akyurek on June 25, 2001 10:24 AM

WolfGirl (or Virginia)

Non-unique nature of your records were a problem. VLOOKUP cannot handle such situations. Since then I cooked up something that might meet your needs.

I'll assume your present sample of data

{"Code","Name";"D","Madison";"D","Johnson";"F","Wilson";"F","Smith";"F","Jackson";"S","Wilder";"S","Adams"}

to be in the range A1:B8.

In C2 enter: =ROW()-1 [ copy down this up to the row where your data ends; sample data ends in row 8. ]

In D2 enter: =IF(A2="F",RANK(C2,$C$2:$C$8)+COUNTIF(C$2:C2,C2)-1,"") [ copy down this up to the row where your data ends; sample data ends in row 8. ]

In E2 enter: =COUNT(D2:D8)

Name E2 NumRecs via the Name Box.

In E3 enter: =MAX(D2:D8)

Name E3 MaxNum via the Name Box.

In F2 enter: =IF(ROW()-1<=NumRecs,INDEX(A$2:A$8,MATCH(MaxNum-ROW()+2,$D$2:$D$8,0)),"")

Copy this to G2 and down up to the last row of data.

You'll have in F2:G4 the following "F"-records:

{"F","Wilson";"F","Smith";"F","Jackson"}

Aladin

======================

Posted by lenze on June 25, 2001 10:28 AM

Wolf Girl:
You can do this using Advanced Filter Option-Filter to new location. See Help for examples