Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup 2 columns, return value from third
I have two tables of pretty much the same format. Column A shows a name that
may or may not be repeated a few times, while column B shows one of four values (say, 1, 2, 3, 4). I need a formula that will return the result of the third column, if it matches Column A and Column B. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup 2 columns, return value from third
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)) slezgus wrote: I have two tables of pretty much the same format. Column A shows a name that may or may not be repeated a few times, while column B shows one of four values (say, 1, 2, 3, 4). I need a formula that will return the result of the third column, if it matches Column A and Column B. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup 2 columns, return value from third
Something like this in say D1, array-entered*:
=INDEX(C2:C10,MATCH(1,(A2:A10="Name1")*(B2:B10=3), 0)) Press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "slezgus" wrote: I have two tables of pretty much the same format. Column A shows a name that may or may not be repeated a few times, while column B shows one of four values (say, 1, 2, 3, 4). I need a formula that will return the result of the third column, if it matches Column A and Column B. |
#4
|
|||
|
|||
Quote:
Hi the Thank you.. this formula works great! I was wondering how, if at all, I can use the IFNA with the Index+Match formula above. I tried using it this way but I ended up with a #NA Quote:
Thanks for your help... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup based on 2 columns; return 1 value | Excel Worksheet Functions | |||
V lookup with 2 criteria to return results for multiple columns | Excel Worksheet Functions | |||
lookup for a value in multiple columns and return a result | Excel Discussion (Misc queries) | |||
Lookup 3 columns and return a result from another column | Excel Discussion (Misc queries) | |||
BIFF - Help!! lookup numbers in multiple columns and return one nu | Excel Worksheet Functions |