Duplicate
 

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

Duplicate
 

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.
--
Thanks,
Shane Devenshire


"purplerose" wrote:

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

Duplicate
 

Hi,

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

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

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"purplerose" wrote in message
...
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