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!!!! |
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!!!! |
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 |
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!!!! |
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 |
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 |
All times are GMT +1. The time now is 06:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com