Find value in one cell in an array

Question

I need to search column A to see if the value in cell B2 is present or not and return the row number where it is. I know there may be a way to search using wildcards but I am having difficulties with that too.

I have tried =Search, =Match, =Lookup, =Find and nothing returns a non-error.

I am using Excel as a part of O365 for Enterprise desktop app on a Win10 PC.

Answer 1

If you want to get all the rows in one cell, you may try this one.

=TEXTJOIN(",",,IFERROR(FILTER(SEQUENCE(ROWS($A$2:$A$9)),ISNUMBER(SEARCH(B2,$A$2:$A$9))),""))

If you just need one result, you may use trip_to_tokyo's formula.

=IFERROR(MATCH("*"&B2&"*",$A$2:$A$9,0),"")

Answer 2

I need to search column A to see if the value in cell B2 is present or not and return the row number where it is. I know there may be a way to search using wildcards but I am having difficulties with that too.

I have tried =Search, =Match, =Lookup, =Find and nothing returns a non-error.

I am using Excel as a part of O365 for Enterprise desktop app on a Win10 PC.

Try:-

=MATCH("*F150*",A2:A9,0)

to display row:-

8

Answer 3

Thank you.

I appreciate your response but in my original post I failed to mention one thing. I need to be able to pull down the formula to search A2:A9 for each of the values in B2, B3, B4, B5, etc.

=MATCH(B2,$A$2:$A$9)
=MATCH(B3,$A$2:$A$9)
=MATCH(B4,$A$2:$A$9)
=MATCH(B5,$A$2:$A$9)
=MATCH(B6,$A$2:$A$9)
=MATCH(B7,$A$2:$A$9)
=MATCH(B8,$A$2:$A$9)
=MATCH(B9,$A$2:$A$9)

I hope this makes sense.

Thanks