Duplicate name in vlookups with multiple resutls

[sherexcel]

Hi everyone,

I'm new here so hoping I post this question correctly and not redundant. I have a sheet where in Column A are names of clients and Column B is the company name the client works for. I get weekly commission reports where the vendors sent me the clients who used their service. My job is to find out which company used the service (Column B) given the clients name (Column A) provided by the vendors. A vlookup chart has worked well until I ran into the issue of a duplicate in the clients name. For example, below is an example chart. There is a John Smith who works for ABC Inc and also another John Smith who works for XYZ LLC. When I do a vlookup, Excel comes back with only the first match (in this case John Smith who works for ABC Inc). Is there an easy formula I can use to somehow get 2 results of ABC Inc and XYZ LLC? Thanks in advance for any input!!!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client Name[/TD]
[TD]Company[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]ABC Inc[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]XYZ LLC[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]ABC Inc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=VLOOKUP(A6,A2:B3,2,FALSE) formula used[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi and welcome to MrExcel,

You've posted a clear question but to answer your question we need to know if the name of the company is also provided in the weekly commission reports. If not and it's just the Client Name, it's impossible to solve. If Client Company is also in both of the files, it can be solved.

 

[James006]

Hi,

Attached is a proposal ...

Test SherExcel.xlsx

HTH

 

[mfexcel]

Try:

=IFERROR(INDEX($B$1:$B$3,SMALL(IF($A$1:$A$3=$A$6,ROW($A$1:$A$3),""),ROWS($A$1:A1))),"Record not found")
Ctrl+Shift+Enter
Copy Down for 2nd match, 3rd match, etc...


Modified so that it works regardless of the starting position of your data:
=IFERROR(INDEX($B$5:$B$7,SMALL(IF($A$5:$A$7=$A$10,ROW(INDIRECT("1:"&COUNTA($A$5:$A$7))),""),ROWS($A$5:A5))),"Record not found")
Ctrl+Shift+Enter
Copy Down for 2nd match, 3rd match, etc...

Hi everyone,

Is there an easy formula I can use to somehow get 2 results of ABC Inc and XYZ LLC? Thanks in advance for any input!!!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client Name[/TD]
[TD]Company[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]ABC Inc[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]XYZ LLC[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]ABC Inc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=VLOOKUP(A6,A2:B3,2,FALSE) formula used[/TD]
[/TR]
</tbody>[/TABLE]