Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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)
https://docs.google.com/spreadsheets/d/1TPXl4qnUcufrbBZ-mEoz6LPEiL_XtXw374r0IDITudg/edit?usp=sharing
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.
