Partial Matches in Google Sheets

user_15324752 0
Viewed 501

having some issues finding partial matches using three different ranges for my list. Please see the attached photo for more details. I've tried Regexmatch, If Search, If Find, If Match, Vlookup. The results I would like to get are on columns B, C, D according to the three different searches we conduct based on columns I, K, M for the linked photo included below. Screen Shot of Google Sheet

Here is an example Spreadsheet

https://docs.google.com/spreadsheets/d/1cET5QItNTP_hueVczzXR-1zFqV1xi5preMa0f2xoD3M/edit#gid=1978015687

For instance, I feel that this formula is close to working, but could not quite get it.

=IF(MATCH(D8, J:J), 1, 0)

Thanks in advance for any help you guys can provide.

google-sheets-formula google-sheets sorting arrays
1 Answers

The formula with most accuracy that can cover you use-cases is below. I added a new sheet in your G-Sheet called "Gangula - SO Example" with the solution implemented in cells with Green border.

=IF(OR(ArrayFormula(IFerror(IF($K$27:$K$39="",0,SEARCH($K$27:$K$39,$H27)),0))),1,0)

This solves more than 90% of the tests that you have added in the Google Sheet.

Sample Screenshot: Sample Screenshot of the solution

user_69082820
Related