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..

  • 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 Dec 19, 2019 at 11:29
  • 1
    Hello, here is a link docs.google.com/spreadsheets/d/… Dec 19, 2019 at 13:32
  • Please change permissions to the sheet to Anyone with the link can access and just Protect the sheet. Dec 19, 2019 at 14:44

1 Answer 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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