SOLVED

Adding a wildcard to a formula

Copper Contributor

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.

 

ajaws0_0-1675283083032.png

 

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 Hans Vogelaar (MVP)
Solution

@ajaws0 

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)

 

@ajaws0 

=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.

partial match.JPG 

Thank you! This worked perfectly.
Glad it worked. You're welcome!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@ajaws0 

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)

 

View solution in original post