Lookup matching two values
 

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

Lookup matching two values
 

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

Lookup matching two values
 

With your data in A1:C4, and 123456 in Cell E1 and 580 in Cell E2, enter this
into Cell E4:
=INDEX(B1:C4,MATCH(E1,A1:A4,0),MATCH(E2,B1:B4,0))

The result is Adam.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"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

Lookup matching two values
 

Lars-Åke's suggestion will work if there's exactly one row that is a match for
both values.

If there are more rows that match both, then you can use another formula to
bring back the value from the first matching row.

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

============
If there is only one match and you're bringing back a number (or 0 if there is
no match for all the criteria), you can use:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10=b1),
(othersheet!c1:c10))

Or if you want to include the "router-1" in the formula:

=sumproduct(--(othersheet!a1:a10=a1),
--(othersheet!b1:b10="router-1"),
(othersheet!c1:c10))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



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

--

Dave Peterson

Lookup matching two values
 

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

Change the ID and Code to a proper cell reference....

=INDEX(D1:D10,MATCH(1,(B1:B10=ID)*(C1:C10=code),0) )

If this post helps click Yes
---------------
Jacob Skaria


"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