Duplicate

[purplerose]

I am trying to use Vlookup to return a value. I can get it to search for a
surname and then return the appropriate value but I have duplicate surnames
(jones etc!) that should return different values.

Currently my formula just returns the first value it finds. Can I lookup 2
columns (first name and surname?)

Would appreciate any help,

Thanks

 

[ShaneDevenshire]

Hi

The problem here is that two people may have the same first and last name
John Smith.

But you can do a two column lookup if you want. Assume that the first names
are in column A, last names in column B starting on row 2. Suppose you enter
the first name of the person you want to look up in J1 last name in K1, and
finally suppose you want to return the age field with is in column C.

=INDEX($C$2:$C$4,MATCH(1,(J1=$A$2:$A$4)*(K1=$B$2:$B$4)))

This formula must be array entered: press Shift+Ctrl+Enter to enter it.


If this helps please click the Yes button.

 

[Ashish Mathur]

Hi,

Considering you have first name and surname in 2 different columns, you can
try this

=lookup(2,1/((range1="firstname")*(range1="surname")),lookup_range)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com