Find value in table based on two inputs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AMDRIT - 30 Dec 2005 15:38 GMT

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

Reply to this Message

Roger Govier - 30 Dec 2005 15:55 GMT

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.

Signature

Regards

Roger Govier

> Given
>
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message

Bernard Liengme - 30 Dec 2005 16:07 GMT

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,C2:D200,2,FALSE) to locate the age
Note that the column with concatenated data may be hidden and lookup will
still work
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Given
>
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message

AMDRIT - 30 Dec 2005 16:45 GMT

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

> Given
>
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message