OR/AND functions returning ranges

New Contributor



does anyone know if there is any way to have OR Function in excel return a range rather than a singe value when it is fed two or more ranges?


E.g. I want XL to return "A" in cell C1 if all cells in range B1:B3000 are either void or 0, "B" otherwise.

As you may figure out, I don't want to manually AND 3000 OR functions, once for each cell in the B1:3000 range.

Also, I would prefer not adding an intermediate column to store the 3000 OR functions, before AND-ing them.


I entered the following formula in C1



I would expect "OR(B1:B3000="",B1:B3000=0)" returns a 3000 rows-1column range, each cell value being either

  • TRUE if the corresponding cell in range B1:B3000 is either void or 0
  • FALSE otherwise


Instead, "OR(B1:B3000="",B1:B3000=0)" returns a single value, being TRUE if any cell in range B1:B3000 is either void or 0, FALSE otherwise.


Thanks to anyone helping

4 Replies

@gpellacani On a slightly smaller scale, is this what you have in mind? (See picture)


Formula used in C1:







@Riny_van_Eekelen thank you so much for helping.

The formula you suggested to enter in C1 didn't exactly do what I needed, as it returns a range rather than a single value, while I need the OR function to return a range, and the AND an IF functions to return a single value (guess I was not clear enough) out if it.


It does the job though, once an AND formula is added:



Guess I need to look back to how arithmetic operators work on logical values in XL


Again, thanks a lot, you kinda saved my day.



@gpellacani Glad you figured it out, although I don't understand what you are trying to achieve.


@Riny_van_Eekelen what I'm trying to achieve is to return A (a single value) if ALL cells in the B1:B15 range are either void or 0, B otherwise (i.e. if ANY cell in the B1:15 range is neither void nor 0).

The function you originally suggested returns A for EACH cell in the B1:15 range which is either void or 0, B otherwise, hence a 15-cell range


Have a great Sunday!