Using Vlookup with duplicate data

[KEOJ]

Message board virgin here, but I need help with an excel issue so excuse me if I don't follow protocol.

I'm using vlookup to return the salesperson by serial number, but run into a problem when my criteria has a duplicate value, but my answers does not.

Here's my example:

Serial#, Salesperson
123 Bill
456 Bob
456 Suzy
789 Bob

How do you work around this?

 

[Smitty]

Welcome to the Board!

How do you work around this?

You don't.

To be reliable you need to have a unique reference/identifier for each individual. Can you key off of sales rep name instead (might require INDEX/MATCH instead of VLOOKUP)?

Why do you have duplicate Serial #'s? Ideally that would be unique and not repeated or recycled.

 

[KEOJ]

The serial number of each unit at our company is unique, but the salesperson is determined by what each unit can do, there are only 2 options. 99% of the units only have one option, so only one salesperson is assigned and vlookup works perfectly. But in our dataland the serial number is the most unique part of data so we sort and analyze by it.

I have not used index/match before. I'll have to play around with it and see what I can figure out.

 

[Smitty]

If you have Serial # and Option, you might be able to use a concatenated reference to create a truly unique ID (i.e. =A1&B1) then base your VLOOKUP off of that.

 

[Aladin Akyurek]

The serial number of each unit at our company is unique, but the salesperson is determined by what each unit can do, there are only 2 options. 99% of the units only have one option, so only one salesperson is assigned and vlookup works perfectly. But in our dataland the serial number is the most unique part of data so we sort and analyze by it.

I have not used index/match before. I'll have to play around with it and see what I can figure out.

Strange situation though...

<TABLE style="WIDTH: 261pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=348 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 width=64>Serial#</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=79>Salesperson</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 18pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 19pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=28> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=64>Bill</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Bill</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num x:fmla="=COUNTIF($B$2:$B$5,F$1)">2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num x:fmla="=COUNTIF($B$2:$B$5,G$1)">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>456</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Serial#</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Serial#</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>456</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Suzy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num x:fmla='=IF(ROWS($F$4:F4)<=F$2,INDEX($A$2:$A$5,IF(F$2>1,SMALL(IF($B$2:$B$5=F$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($F$4:F4)),MATCH(F$1,$B$2:$B$5,0))),"")' x:arrayrange="F4">456</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num x:fmla='=IF(ROWS($F$4:G4)<=G$2,INDEX($A$2:$A$5,IF(G$2>1,SMALL(IF($B$2:$B$5=G$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($F$4:G4)),MATCH(G$1,$B$2:$B$5,0))),"")' x:arrayrange="G4">123</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 align=right x:num>789</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 align=right x:num x:fmla='=IF(ROWS($F$4:F5)<=F$2,INDEX($A$2:$A$5,IF(F$2>1,SMALL(IF($B$2:$B$5=F$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($F$4:F5)),MATCH(F$1,$B$2:$B$5,0))),"")' x:arrayrange="F5">789</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:str="" x:fmla='=IF(ROWS($F$4:G5)<=G$2,INDEX($A$2:$A$5,IF(G$2>1,SMALL(IF($B$2:$B$5=G$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($F$4:G5)),MATCH(G$1,$B$2:$B$5,0))),"")' x:arrayrange="G5"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:str="" x:fmla='=IF(ROWS($F$4:F6)<=F$2,INDEX($A$2:$A$5,IF(F$2>1,SMALL(IF($B$2:$B$5=F$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($F$4:F6)),MATCH(F$1,$B$2:$B$5,0))),"")' x:arrayrange="F6"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:str="" x:fmla='=IF(ROWS($F$4:G6)<=G$2,INDEX($A$2:$A$5,IF(G$2>1,SMALL(IF($B$2:$B$5=G$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($F$4:G6)),MATCH(G$1,$B$2:$B$5,0))),"")' x:arrayrange="G6"> </TD></TR></TBODY></TABLE>

F2, copy across:

=COUNTIF($B$2:$B$5,F$1)

F4, control+shift+enter (not just enter) then copy across and down:

=IF(ROWS($F$4:F4)<=F$2,INDEX($A$2:$A$5,IF(F$2>1,
   SMALL(IF($B$2:$B$5=F$1,ROW($A$2:$A$5)-ROW($A$2)+1),
    ROWS($F$4:F4)),MATCH(F$1,$B$2:$B$5,0))),"")

 

[KEOJ]

I could concatenate the data. I didn't think of that. The concatenate would help me retriew that data I need; however the new problem would be I would not want that concatenated data to appear as my serial number. So do I just hide that column, but use it in my formula?

 

[KEOJ]

Aladin,
I lost you at =IF I have a lot of data that is returned back to me based on the serial number. I only put out sample information since I could not attach anything. My objective is not to count the data. It is to return data based on the serial number and option. Thank you for your formula though.

 

[Aladin Akyurek]

Aladin,
I lost you at =IF I have a lot of data that is returned back to me based on the serial number. I only put out sample information since I could not attach anything. My objective is not to count the data. It is to return data based on the serial number and option. Thank you for your formula though.

As designed, the set up returns all serial numbers associated with a salesperson's name...

 

[Smitty]

So do I just hide that column, but use it in my formula?

Bingo.

But Aladin's much better at figuring out these complexities than I.