Combining VLOOKUP with removing/blanking the result if it is a duplicate of another cell in the same row

[Surreybloke]

Hi,

I'm trying to work out the best way to deal with the issue I've referred to in the subject. I have rows of data where we have one cell where a rep name comes through from the data. We then have another cell where another name should be added based on other criteria, but sometimes it pulls through the same name (correctly) however, I don't want that name to come through for a second time. The VLOOKUP needs to stay in place, but I could do with some help on how to build into that formula, how to exclude it if it matches the name in the other cell on that row.

Thanks,
Surreybloke

I'm using Office 365 and an up to date version of Excel

 

[hagia_sofia]

Could you please post a sample of your data (e.g. 10 rows) including the desired outcome?

 

[Surreybloke]

Could you please post a sample of your data (e.g. 10 rows) including the desired outcome?

Employee ID	Rep_Name_1	Split Amount (%)	Employee ID	Rep_Name_2	Split Amount (%)	Employee ID	Rep_Name_3
210261564	JOE BLOGGS	100​				210210140​	BIG BOSS
210261564	JOE BLOGGS	100​				210210140​	BIG BOSS
210213461	MRS MIGGINS	100​				210210140​	BIG BOSS
210261564	TIMOTHY CLAYPOLE	100​				210210140​	BIG BOSS
210261564	TIMOTHY CLAYPOLE	100​				210210140​	BIG BOSS
210261564	TIMOTHY CLAYPOLE	100​				210210140​	BIG BOSS
210210140	BIG BOSS	100​				210210140​	BIG BOSS
210210140	BIG BOSS	100​				210210140​	BIG BOSS
210261331	HAZEL MCWITCH	100​				210210140​	BIG BOSS
210210140	BIG BOSS	100​				210210140​	BIG BOSS

As you can see, there are some duplicates in names between rep name 1 and rep name 3 and it's in rep name 3 where I want it to return a blank if it is the same name.

 

[hagia_sofia]

Hello, if I understand it correctly you could wrap the current formula into IF function:

IF(VLOOKUP=B2,"",VLOOKUP)

where VLOOKUP represents the VLOOKUP formula that you are using and B2 refers to the "Rep_Name_1" column (i.e. JOE BLOGGS).

 

[Surreybloke]

Hello, if I understand it correctly you could wrap the current formula into IF function:

IF(VLOOKUP=B2,"",VLOOKUP)

where VLOOKUP represents the VLOOKUP formula that you are using and B2 refers to the "Rep_Name_1" column (i.e. JOE BLOGGS).

=UPPER(IF(ISERROR(VLOOKUP(AU1033,'Global Remits'!A:A,1,0)),"",VLOOKUP(AU1033,'Global Remits'!A:B,2,0)))

This is the existing formula in the rep name 3 column. I get the too many arguments error trying to incorporate the additional formula.

 

[hagia_sofia]

Something like this then maybe?

LET(
a,XLOOKUP(AU1033,'Global Remits'!A:A,'Global Remits'!B:B,""),
IF(a=AP1033,"",a)

 

[Surreybloke]

Something like this then maybe?

LET(
a,XLOOKUP(AU1033,'Global Remits'!A:A,'Global Remits'!B:B,""),
IF(a=AP1033,"",a)

Hi, thanks for writing back. Where you have AP1033 in the final part of the formula, is that meant to relate to the rep name 1 field? To add further information which I didn't have time to do yesterday, the lookup to the Global Remits tab, using AU in the formula above, is looking up a product name to a lookup list on the Global Remits tab. It then populates rep name 3 with the name of the person assigned to that product.

Accordingly, I have changed the reference AP1033 to be T1033, which itself has a formula in (a VLOOKUP) to retrieve the correct name for that line. Is the fact that column T has a formula in what is causing your suggestion above to not pull through the name if I change the name in column T? Currently, a blank value is returned regardless of whether the names match or not.

 

[hagia_sofia]

Sorry, I had no clue how the data is structured. The original suggestion was to test whatever is returned in "Rep_Name_3" column against "Rep_Name_1", i.e. IF Rep_Name_3 = Rep_Name_1 then "", otherwise Rep_Name_3. If it is a VLOOKUP formula in Rep_Name_3 then wrap it up into IF statement. Or do I not understand it correctly?

 

[Surreybloke]

Sorry, I had no clue how the data is structured. The original suggestion was to test whatever is returned in "Rep_Name_3" column against "Rep_Name_1", i.e. IF Rep_Name_3 = Rep_Name_1 then "", otherwise Rep_Name_3. If it is a VLOOKUP formula in Rep_Name_3 then wrap it up into IF statement. Or do I not understand it correctly?

No worries at all, it was my fault for not being clearer, but work was so busy I didn't have the chance. I tried making some amendments to your formula yesterday but I couldn't get it to work if the names didn't match as it still produced the blank cell in rep_name_3. I'm not quite sure how to make it work with the IF statement having tried and failed yesterday with that.

 

[hagia_sofia]

XLOOKUP has an optional argument what should be returned if nothing is found - you can change it from "" to something like "not found". Based on your data is the output you are looking for depicted below?