Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple column lookup?
Here is the data I am working with:
LOB C/N # Corporate Marketing 2 Corporate Marketing Critical 1 Corporate Marketing Non-Critical 1 Corporate Safety 1 Corporate Safety Critical 3 Corporate Security 1 Corporate Security Critical 1 How do I search for a lookup a "Corporate marketing" and "Critical" to return the number from column 3? Thanks!!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple column lookup?
hi Paula,
=sumproduct(--(a2:a1000="Corporate Marketing")*(b2:b1000="Critical");(C2:C1000)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "paula k" escreveu: Here is the data I am working with: LOB C/N # Corporate Marketing 2 Corporate Marketing Critical 1 Corporate Marketing Non-Critical 1 Corporate Safety 1 Corporate Safety Critical 3 Corporate Security 1 Corporate Security Critical 1 How do I search for a lookup a "Corporate marketing" and "Critical" to return the number from column 3? Thanks!!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple column lookup?
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't use the whole column. 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)) paula k wrote: Here is the data I am working with: LOB C/N # Corporate Marketing 2 Corporate Marketing Critical 1 Corporate Marketing Non-Critical 1 Corporate Safety 1 Corporate Safety Critical 3 Corporate Security 1 Corporate Security Critical 1 How do I search for a lookup a "Corporate marketing" and "Critical" to return the number from column 3? Thanks!!!! -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple column lookup?
Thank you for answering so quickly..it is still not working though...is the
";" correct? I get an error around that ... excel suggests to change it to a "*" then when it does change, I get a "Value" error...any help??! "Marcelo" wrote: hi Paula, =sumproduct(--(a2:a1000="Corporate Marketing")*(b2:b1000="Critical");(C2:C1000)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "paula k" escreveu: Here is the data I am working with: LOB C/N # Corporate Marketing 2 Corporate Marketing Critical 1 Corporate Marketing Non-Critical 1 Corporate Safety 1 Corporate Safety Critical 3 Corporate Security 1 Corporate Security Critical 1 How do I search for a lookup a "Corporate marketing" and "Critical" to return the number from column 3? Thanks!!!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple column lookup?
AWESOME!! THANKS!!! :)
"Dave Peterson" wrote: 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't use the whole column. 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)) paula k wrote: Here is the data I am working with: LOB C/N # Corporate Marketing 2 Corporate Marketing Critical 1 Corporate Marketing Non-Critical 1 Corporate Safety 1 Corporate Safety Critical 3 Corporate Security 1 Corporate Security Critical 1 How do I search for a lookup a "Corporate marketing" and "Critical" to return the number from column 3? Thanks!!!! -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple column lookup?
Depending on your windows settings, that semicolon (;) could be a comma (,).
And the =sumproduct() suggestion will actually add all the values in C2:C1000 if column A has "corporate marketing" and at the same time, column B = "critical". That means that C2:C1000 will have to be numeric for these rows. The other suggestion picks off the first match it finds and only returns that single value. paula k wrote: Thank you for answering so quickly..it is still not working though...is the ";" correct? I get an error around that ... excel suggests to change it to a "*" then when it does change, I get a "Value" error...any help??! "Marcelo" wrote: hi Paula, =sumproduct(--(a2:a1000="Corporate Marketing")*(b2:b1000="Critical");(C2:C1000)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "paula k" escreveu: Here is the data I am working with: LOB C/N # Corporate Marketing 2 Corporate Marketing Critical 1 Corporate Marketing Non-Critical 1 Corporate Safety 1 Corporate Safety Critical 3 Corporate Security 1 Corporate Security Critical 1 How do I search for a lookup a "Corporate marketing" and "Critical" to return the number from column 3? Thanks!!!! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using multiple criterie in one column to sum another column | Excel Discussion (Misc queries) | |||
Multiple columns of data into one long column | Excel Discussion (Misc queries) | |||
lookup value and return result in column to left | Excel Worksheet Functions | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |