Partial Matches in Google Sheets

2
votes

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.

1
arrayssortinggoogle-sheetsgoogle-sheets-formula
Please share a sheet with toy data and edit permissions. Describe at least one of 1s - what was found and where exactly? - kishkin
Could you share a copy of the spreadsheet with the formulas you tried? Also, on the formula you posted why are you using column L if on the sheet is empty? - Kessy
Here is the example as a spreadsheet (also edited into the original post). There is a better explanation of our problem as well as descriptions of the results we are looking to return. docs.google.com/spreadsheets/d/… (Column L was a typo) @Kessy Thanks for your help, let me know if I can provide any other information. - Zigzagzot
Here is the example as a spreadsheet (also edited into the original post). There is a better explanation of our problem as well as descriptions of the results we are looking to return. docs.google.com/spreadsheets/d/… @kishkin Thanks for your help, let me know if I can provide any other information. - Zigzagzot
What you are trying to achieve? Is your picture present the accurate result which you want same from the attached sheet. Col C D E. and can you open the Edit access to sheet. @Zigzagzot - Rajput

1 Answers

0
votes

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