How can I get a list of Sheet2!A2:A cell values where (Sheet2!B2:B= "Yes") and (Sheet1!B2:B less than 200) (ignoring values that are in Sheet1!A2:A but not in Sheet2!A2:A)


1 Answer 1


I've added a new sheet ("Erik Help") to your sample spreadsheet. There is one formula in A1 (currently highlighted in bright green):

=ArrayFormula({"Matches YES and <200"; IFERROR(FILTER(Sheet2!A2:A,Sheet2!B2:B="Yes",IFERROR(VLOOKUP(Sheet2!A2:A,Sheet1!A2:B,2,FALSE),9^9)<200))})

This formula produces the header (which you can change as you like within the formula) and all results.

FILTER will return any elements of Sheet2!A2:A that match two conditions:

1.) the corresponding cell in Sheet2!B2:B is "Yes"

2.) the value beside a VLOOKUP of that Sheet2!A:A element within Sheet1!A2:B is less than 200.

There are two IFERROR wraps. The innermost one will assign 9^9 (i.e., a ridiculously high number) to any element of Sheet2!A2:A that is not found at all within Sheet1. This will rule it out as being <200. (NOTE: If you want to adapt this formula to return matches where Sheet1!B2:B is greater than a certain number, just delete the 9^9 from this IFERROR clause.

The outermost IFERROR wrap will simply bypass the error that would result if there are zero matches to the FILTER and will instead return null.

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.