Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup to return multiple matching values | Excel Worksheet Functions | |||
Lookup 3 matching values of which one is a range and return a sum | Excel Worksheet Functions | |||
Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
Only text values matching using index/match lookup - data type pro | Excel Worksheet Functions | |||
Matching unsorted lookup values | Excel Worksheet Functions |