multiple column lookup?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paula k - 17 Aug 2006 18:02 GMT

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

Reply to this Message

Marcelo - 17 Aug 2006 18:05 GMT

hi Paula,

=sumproduct(--(a2:a1000="Corporate
Marketing")*(b2:b1000="Critical");(C2:C1000))

hth

Signature

regards from Brazil
Thanks in advance for your feedback.
Marcelo

> Here is the data I am working with:
>
[quoted text clipped - 11 lines]
>
> Thanks!!!!

Reply to this Message

paula k - 17 Aug 2006 18:15 GMT

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

> hi Paula,
>
[quoted text clipped - 18 lines]
> >
> > Thanks!!!!

Reply to this Message

Dave Peterson - 17 Aug 2006 19:45 GMT

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.

> 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
[quoted text clipped - 28 lines]
> > >
> > > Thanks!!!!

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 17 Aug 2006 18:07 GMT

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

> Here is the data I am working with:
>
[quoted text clipped - 11 lines]
>
> Thanks!!!!

Signature

Dave Peterson

Reply to this Message

paula k - 17 Aug 2006 18:25 GMT

AWESOME!! THANKS!!! :)

> If you want exact matches for just two columns (and return a value from a
> third), you could use:
[quoted text clipped - 36 lines]
> >
> > Thanks!!!!

Reply to this Message