4

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

https://docs.google.com/spreadsheets/d/15tpl56RTrGGW3lQ3DVXHjpLhG_vbUPZskcUf-h0YuCE/edit?usp=drivesdk

Improve this question

1 Answer 1

Reset to default
5

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))))
Improve this answer
1
  • 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.
    – Melly Lilly
    Jan 2, 2018 at 1:01

Your Answer

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

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