How to find a Match Using a Wild Card

[Sturgeon]

Hello,

I have the following formula:

=IFERROR(INDEX(Analysis!$B$1:$B$278, SMALL(IF(Analysis!$A$1:$A$278=$C$44, ROW(Analysis!B1:B278),""), ROW()-44)),"")

In cell C44 I have text that I want to find. X*XXXXXXXXXXXXX I want to find the match for this but I need to make the second letter in C44 can be any letter but the rest have to match exactly. Example: F*FR4812033YA70. I would like to find all that match. So I can have: FRFR4812033YA70, FAFR4812033YA70 and so on. Is there a solution to this? Thank you.

 

[DRSteele]

I think this is close. I used function REPLACE to substitute the target data's second character with something else (eg, a *). In C44 I have that same character in the second space, so C44 is F*FR4812033YA70

=IFERROR(INDEX(Analysis!$B$1:$B$278, SMALL(IF( REPLACE(Analysis!$A$1:$A$278,2,1,"*") =$C$44, ROW(Analysis!B1:B278),""), ROW()-44)),"")

 

[Sturgeon]

I think this is close. I used function REPLACE to substitute the target data's second character with something else (eg, a *). In C44 I have that same character in the second space, so C44 is F*FR4812033YA70

=IFERROR(INDEX(Analysis!$B$1:$B$278, SMALL(IF( REPLACE(Analysis!$A$1:$A$278,2,1,"*") =$C$44, ROW(Analysis!B1:B278),""), ROW()-44)),"")

Thank you,

Your suggestion isn't returning any results. The cell is blank.

 

[DRSteele]

It works fine on my sample. Clearly that formula must be invoked as a CTRL+SHIFT+ENTER formula because of the arrays inside, right?

 

[Sturgeon]

It works fine on my sample. Clearly that formula must be invoked as a CTRL+SHIFT+ENTER formula because of the arrays inside, right?

Hi,

Yes - I entered the formula change you gave and did CTRL+SHIFT+ENTER. The Cell is blank after doing these things. Thanks.

 

[Sturgeon]

Hi,

I have tried several things to get the change you recommended to work and I am not having any luck. Would you have any other suggestions? Thanks.