Searching with Wild Cards but backwards.

Hello everyone, this is my first post on this forum.

Right now I am trying to figure out a way to solve my problem and I am hoping someone here can help me out.

In essense, what I want to do is search using wild cards but instead of a wildcard being in the reference, the wild card is in the array that I want to be searched.

For example, I will have a list of numbers (each 10 digits long). In a separate spreadsheet, I will have a database of more numbers. In this database it will include numbers that are 10 digits long AND it will also include numbers that is 5 digits long with the * wildcard after it. So if I search for 1234567890, it will recognize 12345* as a match.

Typically wildcards are used the other way in which I would search 12345* and it will see 1234567890 as a match. But I find that it doesn't work the other way. As far as I have experimented with at least. Does anyone know if they can help? Thanks ahead of time!

Set up a helper column in the sheet with wildcards to search the list of 10 digit numbers using the wildcards those numbers contain.

However, be aware that wildcard matching doesn't work on numeric values, so you will need to set up your list of 10 digit numbers as strings, eg: =1234567890&"" or have your numbers in a column (e.g. X) and then =$X1&"" in the search column.

Then, in the sheet with the list of 10 digit numbers, search for each number's own row number in the helper column of the wildcard sheet.

Thus:

Sheet1 . . . . . . . Sheet2
1234567890 . . . . . 31415*
9876543210 . . . . . 12345*
3141592653


In Sheet2, say Column B, set up "=MATCH($A1,Sheet1!$A:$A,0)" (where $A:$A is whatever column you have used to set up the text values of the numbers)

Then in Column B of Sheet1, set up "=MATCH(ROW(),Sheet2!$B:$B,0)"

This should yield:


Sheet1 . . . . . . . Sheet2
1234567890 . 2 . . . 31415* . 3
9876543210 . #N/A. . 12345* . 1
3141592653 . 1