# QUERY where column IS NOT EQUAL to ANY cell in column A of a second sheet

I want it to pull the max value from column F where the corresponding row has C matching the number 14, and A does not have any exact text matches in any cell in column A of a separate sheet.

I feel I might be using the wrong formula altogether to do what I want.

``````=QUERY('6 Star Gear Sets (Hidden)'!A6:F, "select F where C = 14 AND A != 'Unique Gear (Hidden)'!A6:A order by F desc limit 1")
``````

I am getting a PARSE_ERROR

Here is a link with a copy of the google document I am working on..

`Query` is great for comparison of fields withing the same record (row) but it does not easily do the kind of lookups you want. It is possible to manifacture a long query string `where A <> 'this' and A <> 'that'...` with a separate formula, but this is not an enjoyable exercise.

On the other hand, `filter` is pretty easy to use here:

``````=filter(F:F, C:C=14, isna(match(A:A, Sheet2!A:A, 0)))
``````

says: find all entries in F where the C value is 14 and the value in A does not match anything in Sheet2 column A. The function `match` returns #N/A when the value is not found, and this is what `isna` looks for.

Then it's just the matter of taking max of those:

``````=max(filter(F:F, C:C=14, isna(match(A:A, Sheet2!A:A, 0))))
``````
• Thank you for your help, it has proved quite useful. This is the code I ended up with, '=max(filter('6 Star Gear Sets (Hidden)'!F\$6:F, '6 Star Gear Sets (Hidden)'!C\$6:C=B8, isna(match('6 Star Gear Sets (Hidden)'!A\$6:A, 'Unique Sets (Hidden)'!A\$6:A, 0))))' . Only difference is I used a cell reference instead of the number 14. I wish I could up vote your answer, but my reputation isn't high enough. Jan 2, 2018 at 1:01