CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Hoco Digital
Hoco Digital

Reputation: 13

Potentially complicated formula with FILTER, VLOOKUP and multiple sheets

I am looking at using a filter to pull data from one Google sheet automatically to another Google Sheet.

The problem is, some of the values in the cells contain "," which mismatch my vlookups.

I have figured out the formula I need to automatically filter this, but I can't wrap my head around how to insert it with a filter formula.

EDIT: The below formulas are on a separate sheet to Data1 & Data2 called "Filter Sheet"

Here are the two formulas I'd like to combine:

=filter('Data2'!C2:C,'Data2'!A2:A="uk")

This one cleans up the values with "," in them:

if(REGEXMATCH(A8,","),iferror(VLOOKUP(index(split(A8,",",1),1,1),'Data1'!A2:A,1,0),iferror(VLOOKUP(index(split(A8,",",1),1,2),'Data1'!A2:A,1,0),false)))

**EDIT:The formula above looks to a different sheet (Data1) to see if the "split" result matches the VLOOKUP on the current sheet. If it does not then it goes through a loop to finally find a match on Data1 Sheet.

Essentially, I want the second statement to**

How do I insert this "clean up formula" so it can work with the filter formula values?

EDIT: How can I combine the filter formula with the "if(regex etc. formula" based on the conditions set?

OR is there an easier way of doing this?

If this helps with an easier method:

The reason why I use a filter formula is because not everything in Data Sheet 1 is found in Data Sheet 2, so this smooths that process out.

Your help is greatly appreciated and thank you in advanced.

Upvotes: 0

Views: 253

Answers (1)

Osm
Osm

Reputation: 2891

Try this formula directly

=filter(Data2!C2:C,REGEXEXTRACT(Data2!A2:A, " (uk)")="uk")

Upvotes: 0

Related Questions