SOLVED

# Adding a wildcard to a formula

Copper Contributor

# Adding a wildcard to a formula

Hello,

I am trying to create a search cell for my spreadsheet to search within multiple columns and rows of a table and return the column header of the matched cell. The formula works with only exact matches and I would like to add a wildcard option if possible to allow for partial matches.

This is a very simplified version of my spreadsheet... as a term is entered into top box ("gray") the header is returned ("E"). I am trying to search "yellow" and have B returned but is currently coming up all the headers in a row ("A     B    C    D    E").  I have tried adding "*"& before and after P58 and/or N55:R56 but have been unsuccessful so far.  It would also work if I could somehow match the search value to the first 8 characters of the cells in the table using the LEFT function but I'm not sure how I could write that into the formula...

Here is my current formula:

=IFERROR(INDEX(N54:R54, SUMPRODUCT(MAX((P58=N55:R56)*(COLUMN(N54:R54))))-COLUMN(N54)+1), "")

Any help would be appreciated!

4 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Adding a wildcard to a formula

Something like this maybe. It can be folded up a bit more but this is the idea:

``=UNIQUE(BYCOL(colors,LAMBDA(col,IF(ISNUMBER(XMATCH("*"&L1&"*",col,2)),TAKE(col,1),""))),1,1)``

# Re: Adding a wildcard to a formula

``=IFERROR(IFERROR(HLOOKUP("*"&P58&"*",CHOOSE({1;2},\$N\$55:\$R\$55,\$N\$54:\$R\$54),2,0),HLOOKUP("*"&P58&"*",CHOOSE({1;2},\$N\$56:\$R\$56,\$N\$54:\$R\$54),2,0)),"")``

An alternative could be this formula.

# Re: Adding a wildcard to a formula

Thank you! This worked perfectly.

# Re: Adding a wildcard to a formula

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Adding a wildcard to a formula

Something like this maybe. It can be folded up a bit more but this is the idea:

``=UNIQUE(BYCOL(colors,LAMBDA(col,IF(ISNUMBER(XMATCH("*"&L1&"*",col,2)),TAKE(col,1),""))),1,1)``