Double vlookup

P

Pierre

Hello all,

My problem is quite simple for a lot of you guys :
I have 3 columns :
Name, First name and age
And I want to find the age by giving the name and the first name...
basically, a kind of "Double Vlookup"

Easy ???:cool: :cool
 
A

Andrew

How about joining the Name and First Names together?

For example, if there is a name in A1 and First Name in B1, in anothe
column enter =A1&B1 or =CONCATENATE(A1,B1) and use VLOOKUP to referenc
this column. Unless you have people with the same Name and First Name
this should work.

You can also add spaces such as =A1&" "&B1 or =CONCATENATE(A1," ",B1)
 
P

Pierre

yes, it is what I am using but I would like a trick to avoid th
creation of a new column with the concatenation of my 2 colmns.

Thanks a lot anywa
 
H

Harry Bo

Pierre,
A B C D E F
1 Dave Jones John Smith 36
2 Dave Jones 24
3 24

If your table has the 3 columns C (first name) , D ( Last name) & E ( Age)

In put in Col A first name (dave) and Col B last name (jones) in cell F3 use this formula:
=SUMPRODUCT(--(C1:C2=A1)*--(D1:D2=B1),(E1:E2))
Control, shift and Entered

Harry
 
D

Domenic

Hi Pierre,

Assuming that your data are in Columns A, B, and C, and that your data
starts in Row 2, try,

=INDEX($C$2:$C$10,MATCH(1,(A2:A10=D1)*(B2:B10=E1),0))

which is to be entered using CTRL+SHIFT+ENTER.

D1 contains your criteria for Name

E1 contains your criteria for First Name

Hope this helps!
 
A

Andrew

Hi Pierre,

If you change your mind about concatenating, I have a solution tha
uses OFFSET, MATCH and CONCATENATE. Just a thought ;
 
P

Pierre

Thanks a lot Andrew,
Sounds very exciting.... but perhaps too slow if you have thousand
rows... I am still interrested anyways... and to lazy to find th
"magic" combinaison... ;) :
 
A

Andrew

SUMPRODUCT, I should have guessed! Well, mine was fun too :)

Both Harry and Andy B's formulas work perfectly without an
concatenating. Harry, I still get correct results without enterin
Ctrl, Shift and Enter
 
D

Domenic

Hi Pierre,

Assuming that your data are in Columns A, B, and C, and that your data

starts in Row 2, try,

=INDEX($C$2:$C$10,MATCH(1,(A2:A10=D1)*(B2:B10=E1),0))

which is to be entered using CTRL+SHIFT+ENTER.

D1 contains your criteria for Name

E1 contains your criteria for First Name

Hope this helps!
 
Top