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

 

[maxflia10]

If possible instead of the age that I used, use a column for last names

double.xls
	A	B	C	D	E	F	G
15	Bob	15	130	Bob-@-15	bob	7	115
16	Mary	12	119	Mary-@-12
17	Bob	7	115	Bob-@-7
Sheet1

The trick is to create "unique" values to lookup

 

[jakemelon]

What if creating unique data is not an option? All the lookup data will be imported and cannot be changed.

 

[Yogi Anand]

Then you can use the array formula ...

{=INDEX($C$1:$C$3,MATCH(E1&F1,$A$1:$A$3&$B$1:$B$3,0))}

see the following simulation ...

Book2
	A	B	C	D	E	F	G
1	Bob	15	130		bob	7	115
2	Mary	12	119
3	Bob	7	115
Sheet5

 

[Yogi Anand]

Or, with the same data set in the earlier posts, you can use the following SUMPRODUCT formula ...

=SUMPRODUCT((A1:A3=E1)*(B1:B3=F1)*(C1:C3))

 

[Jaafar Tribak]

Hi,

What if we could tell the LookUp Function what Instance of the LookUp Value to search For in the First Column.Wouldn't that be nice?
Certainly,this would give you much more control.

Well,to achieve this, I have created this UDF Called CustomVLookUpwhich takes an Extra Argument : 'Inst' which determins the Instance or Occurrence of the Value to look for in the First Column of the Table.

The 3 first arguments are the same as the Built in Excel VLookUp Function.

An additional advantage is that if a Match is not found then the Function returns the message : 'No Match'which is more Descriptive than Excel Error messages.

Below is the Code:

Public Function CustomVLookUp(vl, Table As Range, Col, Inst)
    On Error GoTo x
    Set SearchCol = Range _
    (Table.Cells(1, 1), Cells(Table.Rows.Count, 1))
    If Abs(Int(Inst))<> Inst Or Inst > _
    Application.CountIf(SearchCol, vl) Or Inst = 0 Then GoTo x
    Set mtch = SearchCol.Find(vl, LookIn:=xlValues)
    For i = 1 To Inst - 1
        Set mtch = Range(mtch, Cells(Table.Rows.Count, 1)) _
        .Find(vl, LookIn:=xlValues)
    Next
    CustomVLookUp = mtch.Offset(0, Col - 1)
    Exit Function
x:  CustomVLookUp = "No Match"
End Function

Below is a Worksheet Demo with Different Values for the Instance Argument including negative numbers and 0:

turedat.xls
	A	B	C	D	E	F	G	H
1	Name	Age	IQ			Name:	BOB
2	BOB	30	10
3	MARY	20	10			Instance	FunctionResult
4	BOB	50	8			-2	NoMatch
5	BOB	10	4			0	NoMatch
6	BOB	11	20			1	10
7	JOHN	10	2			2	8
8	BOB	68	12			3	4
9	ALI	100	1			4	20
10	MORAD	6	100			8	NoMatch
11	MARY	35	60			5	12
12	BOB	44	74			0.25	NoMatch
13	BOB	15	85			7	85
14	JOHN	84	55			100	NoMatch
15	AMIR	40	85			6	74
16	ALI	77	68			1	10
17
18
Sheet1

Hope this helps.

 

[joelcgpr]

Convert BOB to Bob1, Bob2, Bob3, etc

Column A Column B Column C Column D col. E

BOB =COUNTIF($A$1:A1,A1) =A1&B1 36 115
BOB
BOB
BOB
etc...


_____ =VLOOKUP(A8,A1:E4,5,)



Column A has 4 ocurrences of BOB

Column B will count that ocurrences as 1, 2, 3, 4...

Column C will concatenate A with B (BOB1, BOB2, BOB3, BOB4)
This are unique values that VLOOKUP will understand.

In cell A8 write BOB1, BOB2, BOB3 or BOB4 whichever you are looking for.

VLOOKUP sees A8, Search in A1:E4, When it finds A8 looks for column 5 in this case column E, A comma after number 5 will calculate EXACT match of A8 but if A8 doesn't exist in A1:E4 will return #N/A. Try it and tell me.

_________
From PUERTO RICO with pride

 

[Aladin Akyurek]

What if creating unique data is not an option? All the lookup data will be imported and cannot be changed.

Creating an additional column (using a concatenation formula) does not change the imported data at all. You can even create the additional column in a different worksheet and use it for multi-key/conditional retrieval. I might as well add that this method (see the maxflia post) is in performance terms the cheapest.

 

[dwelleronthethreshold]

The concept behind this UDF is great. Alas, I can not get it to work. Has anyone else tried it?

 

[PaddyD]

see the arbitrary lookup example here:

http://www.cpearson.com/excel/lookups.htm

and an example of using the method to get the n'th match here;

http://www.mrexcel.com/board2/viewtopic.php?p=221768&highlight=lookup#221768

paddy