Search a database with wildcards

[ThatOneDude]

I have a large database that I need to search but I'm struggling with getting this to happen the way I'd like it to.

Let's say the lookup range includes:
ABC123
ABC223
ABC1234
ZABC1234

Then, I have cell A1 that contains the value ABC*23.

When I use the SEARCH function to search the range using A1 as the lookup value, it will return all four of those values because it searches for the cell value anywhere within the lookup range. In this situation, what I would like it to return is ABC123 and ABC223 only. If cell A1 was ABC*23*, then I would expect ABC123, ABC223 and ABC1234. I don't want the ZABC1234 result at all unless the value in A1 starts with Z or *.

Is there a way to search using the exact text in the cell and only including results that have additional characters where the wildcards are located only?

 

[Kerryx]

 

[ThatOneDude]

Thank you for your reply. The thing is, I need this to happen automatically with a formula and I get the same results whether using FIND or SEARCH.

 

[Kerryx]

Would this work for you

Book2
	A	B
1	ABC123	1
2	ABC223	1
3	ABC1234
4	Zabc1234
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=IFNA(MATCH("ABC*23",A1,0),"")

 

[ThatOneDude]

The issue there is that ABC*23 would actually be the value in a cell so the cell reference would be the only thing in the formula. Then, there would be multiple results that I am listing in the same cell using TEXTJOIN. The MATCH function only returns the first result. I'm then joining all the results into one cell using TEXTJOIN.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1
2	ABC123	abc*23*	ABC123	abc*23	ABC123
3	ABC223		ABC223		ABC223
4	ABC1234		ABC1234
5	ZABC1234
6
Data

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(A2:A5,ISNUMBER(SEARCH(" "&B2&" "," "&A2:A5&" ")))
E2:E3	E2	=FILTER(A2:A5,ISNUMBER(SEARCH(" "&D2&" "," "&A2:A5&" ")))
Dynamic array formulas.

 

[ThatOneDude]

Hm.....so, simply adding a space before and after my lookup value and also the values in the lookup table accomplishes my goal. I never thought of that. Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.