Adding a wildcard to a formula

Copper Contributor



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


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






An alternative could be this formula.

partial match.JPG 

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