multiple column lookup?

[Guest]

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!!!!

 

[Guest]

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:

 

[Dave Peterson]

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))

 

[Guest]

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??!

 

[Guest]

AWESOME!! THANKS!!!

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))

 

[Dave Peterson]

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.