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
