Muliple lookup in one cell

R

Roger Govier

Hi Tim

One way
=IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222",A2)),TRUE,FALSE)
copy down
 
D

Dave Peterson

Or just:
=ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2))

To the OP:
=Find() is case sensitive
=Search() is not case sensitive
 
R

Roger Govier

Much neater, Dave!!!

--
Regards
Roger Govier



Dave Peterson said:
Or just:
=ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2))

To the OP:
=Find() is case sensitive
=Search() is not case sensitive
Click to expand...
 
D

David Hilberg

TM,

I think this is just a difference in philosophy. Your formula

=ISNUMBER(SEARCH("aaaa*2222",A1))

is so far the most elegant that could solve the OP's problem precisely
as the OP presented it. The other formulas are longer, but more
flexible, and may be useful to other people (or to the OP himself, if he
didn't completely specify the range of cases).

Just my 2¢.

- David Hilberg
 
M

Mark Lincoln

Your search specification is for "aaaa" followed by any or no
character(s) followed by "2222". If "2222" is not preceded by
"aaaa" (or "AAAA", as SEARCH isn't case-sensitive) anywhere in the
string, the formula will return False.

Mark Lincoln
 
D

Dave Peterson

Roger suggestion include the "copy down" note.

Then you would look at that column to pick out the item that returned true.

If you wanted to return the first value that matched your criteria, you could
use this array formula:

=INDEX(A2:A100,
MATCH(TRUE,ISNUMBER(FIND("aaaa",A2:A100)*FIND("2222",A2:A100)),0))

or

this version based on toothless mama's response:
=INDEX(A2:A100,MATCH(TRUE,ISNUMBER(SEARCH("aaaa*2222",A2:A100)),0))

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 only use the whole column in xl2007.
 
T

TimD

I see I have not asked my question with all the details required.

The data example is on one sheet.

On another sheet I have 3 or more columns.
The header and first data element to search/ find in columns B, C, ... are
aaaa, bbbb
The row data and second data element for a2, a3, a4, ... are 1111, 2222,
2345, ...

I would like b2 to return the value from the first data sheet that has aaaa
and 1111, c2 return bbbb and 1111, etc.
 
D

Dave Peterson

If you don't get any responses, you may want to rephrase your question. I know
that I don't understand it.
 
Top