How to use vlookup when there are duplicate values in the list?

[Harshal]

Hi
I need to do vlookup but there are duplicate values

 

[iferror]

Ok, but there's a piece of info missing:
what do you want to do with the duplicates? Return them all? Return the first? return the n-th?

 

[Somendra Misra]

Hi Harshal,

This is a very regular discussed topic on this forum. Try searching using the Top Right Search Box with keyword "Vlookup duplicate result" or "Vlookup return multiple result".

If you don't find anything which can meet your requirement, write back, somebody will help you.

Regards,

 

[Harshal]

I need the following
Return the first
return the n-th

 

[Harshal]

here is data list.

I need to lookup for Heading1 from Heading2 and required value is Heading3.
i) I need all the values for duplicates
ii) Return the first
iii) Return nth

Heading2     Heading3          Heading1    Vlookup Query
89517191     AN547            FLA013791    ALARMNETGTC3
89517192     AN547            FLA013791    ALARMNETGTC3
89517193     AN583            FLA013791    ALARMNETGTC3
89517194     AN583            FLA013791    ALARMNETGTC3
89517195     AN583            FLA017564    ALARMNETGTC3
FLA012274    AN547            FLA017564    ALARMNETGTC3
FLA013334    AN547            FLA018638    ALARMNETGTC3
FLA013791    ALARMNETGTC3     FLA018638    ALARMNETGTC3
FLA013791    AN548            FLA018638    ALARMNETGTC3
FLA016009    AN548             89517190    #N/A
FLA016927    AN548             89517190    #N/A
FLA017564    ALARMNETGTC3      89517191    AN547
FLA017564    AN548             89517192    AN547
FLA017974    AN548             89517192    AN547
FLA018075    AN548            FLA012274    AN547
FLA018316    AN548            FLA012274    AN547
FLA018635    AN547            FLA013334    AN547
FLA018636    AN547            FLA018635    AN547
FLA018637    AN547            FLA018635    AN547
FLA018638    ALARMNETGTC3
FLA018638    AN549
FLA018639    AN547
FLA018640    AN547
FLA018641    AN547
FLA018642    AN547
FLA018644    AN547
FLA018645    AN547
FLA018647    AN548
FLA018647    AN560

 

[Faseeh]

@Harshal ,

Please see if this helps.

 

[NARAYANK991]

Hi Harshal ,

Looking at your data , let us consider the case of looking up FLA013791 :

Under Heading2 , there are two entries with this value ; under Heading3 , the two corresponding entries are :

ALARMNETGTC3

AN548

Now , if we look at the cases that you have mentioned , we should have :

All values

ALARMNETGTC3

AN548

Return the first

ALARMNETGTC3

Return the n-th , if n = 1

ALARMNETGTC3

Return the n-th , if n = 2

AN548

But now , if you have the item FLA013791 mentioned 4 times under Heading1 , what should be returned in the above 4 cases ? Will it be :

All values

ALARMNETGTC3

AN548

#N/A

#N/A


Return the first

ALARMNETGTC3
ALARMNETGTC3
ALARMNETGTC3
ALARMNETGTC3

Return the n-th , if n = 1

ALARMNETGTC3
ALARMNETGTC3
ALARMNETGTC3
ALARMNETGTC3

Return the n-th , if n = 2

AN548
AN548
AN548
AN548

Can you confirm ?

Narayan

 

[Harshal]

It should be
ALARMNETGTC3
AN548
#N/A
#N/A

 

[Harshal]

Faseeh,
Thanks dude

 

[NARAYANK991]

Hi Harshal ,

In which case , the following formula would work even in the case where the entries not in sorted order , either in column B or in column D :

=OFFSET($C$9,SMALL(IF(D10=($B$10:$B$38),ROW($B$10:$B$38)-9),COUNTIF($D$10:$D10,D10)),0)

entered as an array formula , using CTRL SHIFT ENTER.

Narayan