A user pastes in a value to see if there is a full or partial match. I need to do a vlookup and keep removing characters until there is a match. A full match of something like test1.test2.test3 is no problem because it's a full match to my list. But if someone pastes in something like test1.test2.test3.test4, I need to remove a character one at a time from the end until there is a match. So in this example, it would match test1.test2.test3 and return that result.
Conceptually I see this as a for loop that counts the characters using len, using left to remove the number of characters from the end based on the current iteration, and doing vlookups until returning the value when true. But I'm not sure how to do this in Google Sheets.
CodePudding user response：
This formula will give you the matching value that was found in the data
(i.e. test1.test2.test3)
=FILTER([column_with_data], REGEXMATCH([cell_with_pasted_value_to_look], [column_with_data]))
This formula will give you the matching data and the cell reference where it was found
(i.e. test1.test2.test3 @ $A$4)
=FILTER([column_with_data], REGEXMATCH([cell_with_pasted_value_to_look], [column_with_data]))&" @ "&CELL("address",INDEX([column_with_data],MATCH(FILTER([column_with_data], REGEXMATCH([cell_with_pasted_value_to_look], [column_with_data])),[column_with_data],0),1))
Simply copy & paste any of the above formulas next to the cell where users paste a value to look. Then, replace the two references in the square brackets [ ] with the proper coordinates in your sheet:
- replace
[column_with_data]with the coordinates of the column containing all the stored data (i.e.A1:A) - replace
[cell_with_pasted_value_to_look]with the absolute ($col$row)coordinates of the cell where users paste the value to look (i.e.$B$1)
CodePudding user response：
Would it be a problem to download the data from Google sheets, transform the file type to use the for loop in another software, and re-upload? I think your idea for a for loop would work.
It might be quicker if this is a long term project, but not so great if the client is continually monitoring/uploading.