Lookup using first last name, need conceptual awnser

[Guest]

I have 3 columns, lets say, A,B,and C. I have a search Field for first name
and last name. lets say D1,E1, I need to be able to have D1 and E1
match up in column a and column b and return the value on column c ONLY if
column A AND column B match up, cause theres alot of johnsons, or people with
the same last name.
A B C D
E
First Name Last Name Needed Data Find First Find
Last

I've been trying to use Vlookup Formula
=IF(ISNA(VLOOKUP($D$1,$A$1:$C$1000,1,FALSE)&VLOOKUP($E$1,$B$1:$C$1000,1,FALSE))=TRUE,"No Match",???????

Not only can I not return the value from column C, Vlookup uses the first
johnson that it finds then the first chris, so in a list like so it would
return a positive find

Chris Beamer
Joe Johnson

Whereas chris johnson does not exsist=(

 

[Guest]

Chrihas,

Try this:

=IF(ISNA(MATCH(1,(D1=$A$1:$A$1000)*(E1=$B$1:$B$1000),0)),"No
Match",INDEX($C$1:$C$1000,MATCH(1,(D1=$A$1:$A$1000)*(E1=$B$1:$B$1000),0)))

HTH,
Ryan

 

[Guest]

One more thing, this formula must be array-entered (ctrl-shift-enter)

 

[Alan Baum]

Ryan:

I found your response on using the match and index
formulae to deal with multiple vlookup fields. I
tried it and got the "No Match" result when I should
not have. I looked up array entered and hit
cntrl-shift-enter before I entered the formula and
then I did it again after and it didn't seem to do
anything.

Did I mess up the conversion to array formulae?

Thanks.

Alan Baum

 

[Guest]

Alan,

Is it possible that the source and lookup data may not be exactly the same?
Look for differences in capitalization, leading/trailing spaces, other things
like that. The values being searched must be exactly the same as the values
in your search criteria.

Ryan