Muliple lookup in one cell

[Guest]

My data sample:
(e-mail address removed)
aaaa-*5#8-2222.zip
bbbb-0987-2345.dat
bbbb-*458-2222.xls

I want to return the cell that contains aaaa and 2222.

 

[Roger Govier]

Hi Tim

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

 

[Dave Peterson]

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

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

 

[Guest]

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

 

[Roger Govier]

Much neater, Dave!!!

--
Regards
Roger Govier

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

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

 

[Guest]

My is much neater than Dave.

Much neater, Dave!!!

 

[Dave Peterson]

Until the 2222 appears before the aaaa.

My is much neater than Dave.

 

[Guest]

Give me a break.

It still work even 2222 appears before the aaaa

 

[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

 

[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

 

[Dave Peterson]

Did you even try it?

Give me a break.

It still work even 2222 appears before the aaaa

 

[Guest]

This does not seem to return a cell, it returns a calculated value.

 

[Guest]

This does not seem to return a cell, it returns a calculated value.

 

[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.

 

[Guest]

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.

 

[Dave Peterson]

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