OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Produce a result using partial match from data within a specified cell

  • Thread starter Thread starter Tanya Vander Vecht
  • Start date Start date

Tanya Vander Vecht

I am looking to pull the name of a customer from the tab "Contact_Data" based on a partial match to the data in column C of the tab "Chart_Graph". I will be using this later to produce reports that show the last time a specific customer was contacted, but this is the key piece to getting to that point. Right now I need to focus on solving this equation first.

Please see link to the sample sheet that I have created.

Access Sample Sheet

I have attempted to be as detailed as possible and this issues impacts a very important project I am working on. I would ask that you please ask me questions before simply closing this question as that makes this process much more difficult than it needs to be and I am checking back frequently so I will be responsive.

I have tried FILTER combined with FIND, SEARCH, REGEXMATCH, and I have tried VLOOKUP which is what is in the sample sheet in Column D. All produce some results but not all. Changing FALSE to TRUE, produces results, but 98% incorrect results.

The formula in the sheet currently which produces SOME results in column D: =IFERROR(VLOOKUP(C2&"*",Contact_Data!$A:$A,1,FALSE)," ")

if TRUE produces mostly incorrect resuts: =IFERROR(VLOOKUP(C2&"*",Contact_Data!$A:$A,1,TRUE)," ")

I have also tried with no success: =FILTER(Contact_Data!A:A, FIND(C2, Contact_Data!A:A)) =FILTER(Contact_Data!A:A, REGEXMATCH(Contact_Data!A:A,C2))

The following formula works until I pull it down the column, then it only works in the last cell because it spills over. I do not know how to limit it to 1 response in the cell where the formula is contained. See column G =FILTER(Contact_Data!A:A, SEARCH(Contact_Data!A:A,C2)) Error produced when expanding: "Array result was not expanded because it would overwrite data in G4."

I have also tried using QUERY. I again get some results, but it is not working to produce a result in each cell in the column where there is data to test in Column C and it should be able to. Please see column F: =QUERY(Contact_Data!A:C, "select A where A contains '"&SPLIT(C2, ",")&"'", 0)

**So I need one of three solutions: **

Column D - VLOOKUP that produces a consistent result. Pulling the correct name from the Contact Data Tab, column A that partially matches the same embedded within the notes in Chart_Graph column C.


Column E - Filter(Search that allows the formula to produce a result in each cell in column E based on the partial name in column C.


Column F - Query that is able to produce an accurate result in each cell in the range where data exists in column C

Continue reading...