On Tue, 3 Nov 2009 14:21:01 -0800, Nick Ng
wrote:

Hi,

Assuming I have the following table:

A.........B............C...........D
........(ID).......(Code)...(Name)
1.....123456.....0580.....ADAM
2.....123456.....0581.....JOHN
3.....123456.....0582.....GARY
4.....678901.....0580.....SARA
5.....890123.....0580.....KENN
6.....890123.....0581.....LARS




I'm trying to create a formula to look through the table, and return the
(Name) value that matches the corresponding (ID) and (Code) values.

For example, if ID=123456, and Code=0581, the result would be JOHN.

I tried a sumproduct equation from John C in another thread but it returned
a value of 0:

=SUMPRODUCT(--(B1:B^=ID),--(B1:C6=Code),(D1:D6))

Might it be because Column D (Name) is in text?

Thanks!

-Nick



Try this formula:

=INDEX(D1:D6,SUMPRODUCT(--(B1:B6=123456),--(C1:C6=581),ROW(D1:D6)))

Hope this helps / Lars-Åke