Formula problem referencing cells with #N/A

[NH12345]

Hi, I’m trying to create a formula that involves using IF and OR. The problem is that the columns the formula references have a lot of #N/A errors in them, so the result of the formula ends up being #N/A if there is an #N/A in either column H or column I. It just seems to disregard what is in the other column if even one of the columns has an #N/A. I’m trying to use a formula like the following:


=IF(OR(H2="Apples",I2="Apples"),"Red",B2)

The #N/As are coming from VLOOKUP formulas that were already in the spreadsheet. I guess I could change the VLOOKUPs so that they will give a result such as “Not Found” or a blank instead of #N/A, but feel like I might be missing something obvious so that the #N/As can stay but the formula will still work.

Is there any way to do this? Thanks in advance for any information.

 

[sheetspread]

Try:

=IF(COUNTIF(H2:I2,"Apples")>0,"Red",B2)

 

[NH12345]

Try:

=IF(COUNTIF(H2:I2,"Apples")>0,"Red",B2)

Thank you! That works great!