Need Formula Help Please

[young1tj]

I'm completely stumped on my current formula issue. No matter how I try and manipulate the data the result still ends the same with the dreaded #NA error.

Worksheet 1: D E F V

First name	Last name	Full Name	Microphone
John	Smith	John Smith	#N/A​

Worksheet 2: A B C

Last	First	Full Name
Smith	John	John Smith

So I'm trying to populate data in the microphone column with a yes or no. I'm comparing 2 lists of data based off first and last names. If the name exists in worksheet 1 and 2 then populate microphone with "Yes" if not then "No". I did a join on both worksheets to combine the first and last names into the Full Name. I'm trying to use VLOOKUP with FALSE so I get an exact Match but it returns #NA. If I use TRUE I get a different name. Here is my current formula:

=VLOOKUP(F4,'Physician Names'!$A$1:$C$897,3,FALSE)

I'm hoping someone here may be able to help me with this issue as I'm just getting frustrated with this error.

 

[Fluff]

Hi & welcome to MrExcel.
Vlookup looks up the value in the 1st column, so it needs to be

=VLOOKUP(F4,'Physician Names'!$C$1:$C$897,1,FALSE)

 

[young1tj]

Hi & welcome to MrExcel.
Vlookup looks up the value in the 1st column, so it needs to be

=VLOOKUP(F4,'Physician Names'!$C$1:$C$897,1,FALSE)

It still came back with the #NA error after switching from 3 to 1, but I am curious as to the column change. F is my concatenated string in worksheet 1 and column 3 would be my concatenated name in worksheet two. Shouldn't it be looking for the exact same name in both sheets to populate the data I'm looking for?

 

[Fluff]

Shouldn't it be looking for the exact same name in both sheets to populate the data I'm looking for?

That's what it is doing. If you are getting #N/A errors then the names are not an exact match.

 

[dreid1011]

If you're only trying to match the names, and not return a value in table 2 to table 1, then how about:

=IF(IFERROR(MATCH(F4,'Physician Names'!$C$1:$C$897,0),0),"YES","NO")

 

[young1tj]

If you're only trying to match the names, and not return a value in table 2 to table 1, then how about:

=IF(IFERROR(MATCH(F4,'Physician Names'!$C$1:$C$897,0),0),"YES","NO")

Thank You!!! This produced the result I was looking for. I was trying to dive into INDEX and MATCH functions, but I could not make heads or tails out of what was actually being required.

 

[dreid1011]

Lookups and Index are used to pull a value from one table to another. Match just tells you if a value exists in another list.

 

[young1tj]

Lookups and Index are used to pull a value from one table to another. Match just tells you if a value exists in another list.

As I'm double checking my list, I'm noticing that I do have matches in both lists that aren't marking YES. They are same spelling in both spaces so I'm not sure what other check I can look into.

 

[dreid1011]

As I'm double checking my list, I'm noticing that I do have matches in both lists that aren't marking YES. They are same spelling in both spaces so I'm not sure what other check I can look into.

Are they also duplicate values? Could you post some of the data that is not working as you expect?