Find value in table based on two inputs

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
Fri Dec 30 09:55:57 CST 2005

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.
--
Regards

Roger Govier


"AMDRIT" <amdrit@hotmail.com> wrote in message
news:e02CtcVDGHA.1180@TK2MSFTNGP09.phx.gbl...
> 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
>
>

Bernard
Fri Dec 30 10:07:28 CST 2005

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"AMDRIT" <amdrit@hotmail.com> wrote in message
news:e02CtcVDGHA.1180@TK2MSFTNGP09.phx.gbl...
> 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
>
>

AMDRIT
Fri Dec 30 10:45:17 CST 2005

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


"AMDRIT" <amdrit@hotmail.com> wrote in message
news:e02CtcVDGHA.1180@TK2MSFTNGP09.phx.gbl...
> 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
>
>