Forum Discussion
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!
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)
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)- Thank you! This worked perfectly.
- Glad it worked. You're welcome!
=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.
