Lookup using first last name, need conceptual awnser

  • Thread starter Thread starter Guest
  • Start date Start date
G

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=(
 
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
 
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
 
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
 
Back
Top