1

Hello I need to compare two columns and output all missing data into a third column.

The problem is that I need to also filter the first column for only certain cells that start with two letters

This is what put together and was expecting to work but I am not sure whether I am using incorrect formulas or the identificator for the starting text is not working..

=ArrayFormula(FILTER(D3:D;ISERROR(MATCH(REGEXMATCH(D3:D;"MY.");A2:A;0))))
  • Column "D" contains all data to be filtered
    • it contains cells starting with "MY", "NY", and "FY"
  • Column "A" contains all data to be compared to
    • this one may be missing some cells "MY...." and that is what I need this formula for
  • So do you want col D with results that do not start with MY, NY,FY? Please share a test sheet with expected results – marikamitsos Dec 19 '19 at 11:29
  • 1
    Hello, here is a link docs.google.com/spreadsheets/d/… – Marek Marhoul Dec 19 '19 at 13:32
  • Please change permissions to the sheet to Anyone with the link can access and just Protect the sheet. – marikamitsos Dec 19 '19 at 14:44
1

You could use a query formula instead:

=QUERY(A1:D14 ;"SELECT D WHERE D like 'MY%' and not A like 'MY%'";1)

Please read more about QUERY

Note that if you use a different Locale in your sheet you would have to change ; to , for the formula to work.

query for missing data

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.