IF/AND/ISBLANK

C. Perry

I have a Master sheet with 2 columns (A column with account numbers and B column with some blank cells and some cells with the letter Y ). I also have my separate review sheet with column C with account numbers. I want to reference the Master sheet to determine if B column has a blank cell and if so does the account number on that row match any of the accounts numbers on my review sheet row. If it does I want it to return Yes and if not No on the review sheet. I created a column on the review sheet for the response. I am using this formula but am getting "#incorrect argument set". =IF(AND(ISBLANK({Column B}, IF(COUNTIF({Column A}, Column[email protected]) > 0, "Yes", "No")))). Please advise the issue with my formula or a better formula.

Nick Korna

Hi@C. Perry

Would something like this work for you?

=IFERROR(IF(VLOOKUP([email protected], {Column A + Column B}, 2, false) = "", "Yes", "No"), "Not on master list")

If the account number in C is found in column A and column B is blank: Yes

C is found and B is filled: No

C is not found: Not on master list

You can obviously change the text as desired (for example changing the "Not on master list" to "" to show nothing and the check for if the value is blank/not to display a value if the account number is found.

Hope this helps, just ask if you've any questions.

Kaveri Vipat

Hi Noella

I hope you are doing well and safe.

Please try this formula - =IFERROR(IF(INDEX({B Column}, MATCH([Column C]@row, {A Column}, 0)) = "", "Yes", "No"), "")

I hope this is useful to you, please let me know if you need any other help.

Have a Good Day!

C. Perry

Thank you. The formula worked. I made a few changes.=IFERROR(IF(INDEX({Option Exception Master List Range 1}, MATCH([email protected], {Option Exception Master List}, 0)) = "N", "Yes-Not Discretionary", ""), "Not on List").My only issue now is if the master listcolumn Bhas a Y I need for the above formula to also use if = "Y" "Yes-Discretionary". I need to combine the two below formulas.

=IFERROR(IF(INDEX({Option Exception Master List Range 1}, MATCH([email protected], {Option Exception Master List}, 0)) = "N", "Yes-Not Discretionary", ""), "Not on List").

=IFERROR(IF(INDEX({Option Exception Master List Range 1}, MATCH([email protected], {Option Exception Master List}, 0)) = "A", "Yes-Discretionary", "").

Another problem I was having was if the cell was blank and no number it returned want ever was in the""space so I put this there to return a blank space. I need for it to return a empty space if the cell is blank.