lookup, search?

[Guest]

I'm looking how to achieve this thru any of the excel functions like lookup,
search etc
so that I can add this function to a macro. I have to find column A values
occuring anywhere inside Column b, c, d

Ex A B C
D
Rio 2 Carona Rep Ford Teac
Carona Belowa Mazda Mitsh
Sung Renault Pana Tos

So I like to extract Row 1 since B1 Contains "Carona" from B2.

Thanks in Advance

 

[Domenic]

F1, copied over to G1 and H1:

=INDEX(B:B,SUMPRODUCT((ISNUMBER(FIND("*&E1&*",$B$1:$D$3)))*ROW($B$1:$D$3)
))

....where E1 contains your criteria, such as "Carona".

Hope this helps!

 

[Domenic]

Correction...

=INDEX(B:B,SUMPRODUCT((ISNUMBER(FIND(E1,$B$1:$D$3)))*ROW($B$1:$D$3)))

 

[Domenic]

Okay...it doesn't look like this is going to be my day. Try the
following instead...

E1:

=COUNTIF(B13,"*"&E2&"*")

E2: enter your criteria, such as "Carona"

F2, copied across:

=IF($E$1>0,INDEX(B:B,SUMPRODUCT((ISNUMBER(FIND($E$2,$B$1:$D$3)))*ROW($B$1
:$D$3))),"")

 

[Guest]

Thanks Domenic, but I don't really understand how these functions work.
For now I'm using split method and matching.
It probabaly takes longer time to run with what I'm doing.