If with fuzzy lookup?

[mahmed1]

I know with countif you can have a fuzzy lookup i.e

=COUNTIF($A$1:$A$6,"*"&F1&"*") = 3
F1 = Harry

A1:A6

HARRY
JAMES
BEN AND HARRY
BEN
MARK AND HARRY
MARK

Is there a way i could add an OR criteria for the criteria range in Countif

i.e =COUNTIF($A$1:$A$6,OR("*"&F1&"*","*"&F2&"*") )

Another quick question

How can i look at the range A1:A6 and have it equal to a fuzzy lookup and then give me that row number where it matches?

e.g

A1:A6

HARRY
JAMES
BEN AND HARRY
BEN
MARK AND HARRY
ME AND JAMES

F1 = HARRY

LOOK THROUGH THE RANGE A1:A6, AND TELL ME THE ROW NUMBERS WHERE I HAVE HARRY IN THERE, IN MY CASE 1,3 AND 5 (i can then use the small function and rows and index to give the data the data in order

HARRY
BEN AND HARRY
MARK AND HARRY

IF POSSIBLE CAN I HAVE 2 CRITERIAS SUCH AS

LOOK THROUGH THE RANGE A1:A6, AND TELL ME THE ROW NUMBERS WHERE I HAVE JAMES AND BEN

IN MY CASE 2, 3, 4 AND 6

JAMES
BEN AND HARRY
BEN
ME AND JAMES

 

[mahmed1]

please bit more patient...

If you change D1:D2 to, say D1:E1, you can drop TRANSPOSE.
TRANSPOSE(D1:D2) or D1:E1 allows SEARCH to create 2-column result, as it should be: One column for James, another for Ben.

Hope this helps.

sorry aladin

thank you so if i was to do d1:f1 i can search for 3 column lookup
does the 3 column treat it like an oe function
eg search for d1 or e1 or f1 in range a1:a6?

 

[mahmed1]

If i were to take the transpose function out, how will the search treat it as a lookup? Singular lookup?

 

[Aladin Akyurek]

sorry aladin

thank you so if i was to do d1:f1 i can search for 3 column lookup
does the 3 column treat it like an oe function
eg search for d1 or e1 or f1 in range a1:a6?

Yes...

 

[Aladin Akyurek]

If i were to take the transpose function out, how will the search treat it as a lookup? Singular lookup?

It's a search for each item...