# Extracting data with VLOOKUP. How to handle duplicates?

This is a discussion on Extracting data with VLOOKUP. How to handle duplicates? within the Excel Questions forums, part of the Question Forums category; VLOOKUP only allows you to look up the first occurence of data. What if the first one is not the ...

1. ## Extracting data with VLOOKUP. How to handle duplicates?

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"

Jake

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

******** ******************** ************************************************************************>
 Microsoft Excel - double.xls ___Running: xl97 : OS = Windows NT 4
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D15G15D16D17 =

A
B
C
D
E
F
G
15
Bob15130Bob-@-15bob7115
16
Mary12119Mary-@-12***
17
Bob7115Bob-@-7***
 Sheet1 *

[HtmlMaker 2.20BETA] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The trick is to create "unique" values to lookup

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

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

******** ******************** ************************************************************************>
 Microsoft Excel - Book2 ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G1 =

A
B
C
D
E
F
G
1
Bob15130*bob7115
2
Mary12119****
3
Bob7115****
 Sheet5 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

5. 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))

6. 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:

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:

******** ******************** ************************************************************************>
 Microsoft Excel - turedat.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G4G5G6G7G8G9G10G11G12G13G14G15G16 =

A
B
C
D
E
F
G
H
1
NameAgeIQ**Name***:BOB*
2
BOB3010*****
3
MARY2010**InstanceFunction*Result*
4
BOB508**-2No*Match*
5
BOB104**0No*Match*
6
BOB1120**110*
7
JOHN102**28*
8
BOB6812**34*
9
ALI1001**420*
10
11
MARY3560**512*
12
BOB4474**0.25No*Match*
13
BOB1585**785*
14
JOHN8455**100No*Match*
15
AMIR4085**674*
16
ALI7768**110*
17
********
18
********
 Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hope this helps.

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

8. Originally Posted by jakemelon
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.

9. ## Re: Extracting data with VLOOKUP. How to handle duplicates?

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

10. ## Re: Extracting data with VLOOKUP. How to handle duplicates?

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/viewto...=lookup#221768

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•