Find value in table based on two inputs

[AMDRIT]

Given

A1: Name B1: Dept C1: Age
A2: Henry B2: 501 C2: 28
A3: Stan B3: 201 C3: 19
A4: Mary B4: 101 C4: 22
A5: Larry B5: 301 C5: 29


how would write a formula to find the age of 'Mary' in dept '101'?

vlookup doesn't seem to support multiple compares.


Thanks

 

[Roger Govier]

Hi

Try
=SUMPRODUCT(--(A2:A5="Mary"),--(B2:B5=102),--(C2:C5))
or better still, put Name required in cell D1 and Dept required in E1
then
=SUMPRODUCT(--(A2:A5=D1),--(B2:B5=E1),--(C2:C5))
Change values in D1 and E1 for other selections.

 

[Bernard Liengme]

Assume the data is in A1:C200 with header in row 1
Insert new column C; in C2 enter =A2&B2 and copy down the column
With name to be found in G1 and dept in H1 use
=VLOOKUP(G1&H1,C2200,2,FALSE) to locate the age
Note that the column with concatenated data may be hidden and lookup will
still work
best wishes

 

[AMDRIT]

Thanks for the quick responses gang. I really appreciate it. Sometimes I
can't see the forest for the trees.