Extracting data with VLOOKUP. How to handle duplicates?

[jakemelon]

VLOOKUP only allows you to look up the first occurence of data. What if the first one is not the one I want to extract?

For example:


Name | Age | IQ |
--------------------------------
Bob 15 100
Mary 20 96
Bob 7 110


=VLOOKUP("Bob",A2:C4,3)

I am using VLOOKUP to find BOB and extract his IQ. But VLOOKUP will find the first instance of BOB and return 100 as the result.

I need it to show me the BOB #2 IQ because he is less than 10 years old. I'd like the result to be "110"

Sorry about this lame illustration, does it make sense as to what I am looking for? how would I accomplish this task?

Jake

 

[cranebird]

dwelleronthethreshold! I was able to create a UDF which worked for me and handled the duplicate values nicely. See below for code.

[FONT=courier new]Public Function CustomVLookUp(VL, Table As Range, Col, Inst)[/FONT]
[FONT=courier new]    On Error GoTo x[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]    Set SearchCol = Table.Columns(1)[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]    If Abs(Int(Inst)) <> Inst Or Inst > _[/FONT]
[FONT=courier new]    Application.CountIf(SearchCol, VL) Or Inst = 0 Then GoTo x[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]    Set mtch = SearchCol.Find(VL, LookIn:=xlValues)[/FONT]
[FONT=courier new]    For i = 1 To (Inst - 1)[/FONT]
[FONT=courier new]        Set mtch = SearchCol.Find(VL, After:=mtch, LookIn:=xlValues)[/FONT]
[FONT=courier new]    Next[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]    CustomVLookUp = mtch.Offset(0, Col - 1).Value[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]    Exit Function[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]x:  CustomVLookUp = ""[/FONT]
[FONT=courier new]End Function[/FONT]

 

[John Col]

Dear Jake,

Have you already a clear solution to this problem?
I am having a bit of the same problem here.

See my post of today,
Thanks,
John

http://www.mrexcel.com/forum/excel-questions/876675-vlookup-duplicates.html

 

[cranebird]

Dear Jake,

Have you already a clear solution to this problem?
I am having a bit of the same problem here.

See my post of today,
Thanks,
John

http://www.mrexcel.com/forum/excel-questions/876675-vlookup-duplicates.html

John,

Not sure if you are linking to the solution. Hope you were able to find the UDF I coded above helpful. Let me know if you have any questions. I didn't put any comments in the code.