multiple-condition lookup

  • Thread starter Thread starter Jocelyn
  • Start date Start date
J

Jocelyn

Hi folks,

I'd like to do a multiple-condition lookup. I have the following
columns:

N O P Q
StyleText Other Text Origin Origin Text

The column OriginText contains states. I'd like to return a result in
the column Origin based on examining the columns OriginText and
StyleText.

I originally did a LOOKUP (with criteria located on a separate sheet –
Lookup_Vector: Sheet3!A2:A32 and Result_Vector: Sheet3!B2:B32) that
examined OriginText and returned "California" in the column Origin if
the text "CALIFORNIA" or the text "UNSPECIFIED" were found. It
returned "Other" if it was any other of a series of known geographies.
Unfortunately, after I did that, I discovered that some of the
"UNSPECIFIED" were *not* from California – and the data that indicate
that are in the StyleText column.

Those data are the text strings "2 1/4 in" "2 1/2 in" and "2 3/4 in"

I still need to get the results of the lookup I mentioned above, but
in addition I'd like the formula to do a LOOKUP, and if the lookup
value is "UNSPECIFIED" to evaluate the corresponding cell in the
column Origin, and if it contains one of those three text strings, to
return the word "Other"

Is there a way I can do a nested LOOKUP, a LOOKUP in conjunction with
IF and OR, or an INDEX and MATCH to return the answer desired in
column Origin?

Many, Many TIA,
Jocelyn
 
(e-mail address removed) (Jocelyn) wrote in
Is there a way I can do a nested LOOKUP, a LOOKUP in conjunction with
IF and OR, or an INDEX and MATCH to return the answer desired in
column Origin?
Click to expand...

Yes. Look into the Database Functions (DFUNCTIONS).
 
Jonathan, could you give me a little bit more sense of what your
thinking was, please? I read up on database functions today and
couldn't figure out what your suggestion was leading to.

Thanks,
Jocelyn
 
(e-mail address removed) (Jocelyn) wrote in
I'd like to do a multiple-condition lookup. I have the following
columns:

N O P Q
StyleText Other Text Origin Origin Text

The column OriginText contains states. I'd like to return a result in
the column Origin based on examining the columns OriginText and
StyleText.

I originally did a LOOKUP (with criteria located on a separate sheet –
Lookup_Vector: Sheet3!A2:A32 and Result_Vector: Sheet3!B2:B32) that
examined OriginText and returned "California" in the column Origin if
the text "CALIFORNIA" or the text "UNSPECIFIED" were found. It
returned "Other" if it was any other of a series of known geographies.
Unfortunately, after I did that, I discovered that some of the
"UNSPECIFIED" were *not* from California – and the data that indicate
that are in the StyleText column.

Those data are the text strings "2 1/4 in" "2 1/2 in" and "2 3/4 in"

I still need to get the results of the lookup I mentioned above, but
in addition I'd like the formula to do a LOOKUP, and if the lookup
value is "UNSPECIFIED" to evaluate the corresponding cell in the
column Origin, and if it contains one of those three text strings, to
return the word "Other"
Click to expand...

I'm sorry, I had completely misread your question before.

Try this (remove the extra spaces):

=if(Q2="CALIFORNIA",
"California",
if(Q2="UNSPECIFIED",
if(ISERROR(FIND("#"&N2&"#","#2 1/4 in#2 1/2 in#2 3/4 in#")),
"California",
"Other"),
"Other"))

You may need to replace one or both of the "other" with your lookup.
 
Jonathan, thanks a million. I've been in meetings most of the week
and haven't been working on the project much, but now that I'm back to
it, I have a solution thanks to your help. Here's what I ended up
using:

=IF(Q2="CALIFORNIA","California",IF(Q2="WESTERN","California",IF(Q2="UNSPECIFIED",IF(ISERROR(FIND("#"&N2&"#","#2
1/4 in#2 1/2 in#2 3/4 in#")),"California","Other"),"Other")))

I had more conditions once I actually got into the filtering than I
had originally recognized. And I didn't know about ISERROR - thanks
for enlightening me. I've read about it and already found other
places to use it.

Best,
Jocelyn
 
Back
Top