How to find a Match Using a Wild Card

Sturgeon

New Member
Joined
Mar 2, 2018
Messages
6
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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)),"")
 
Upvote 0
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.
 
Upvote 0
It works fine on my sample. Clearly that formula must be invoked as a CTRL+SHIFT+ENTER formula because of the arrays inside, right?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top