 # How to test a value

For every row:
In col A I need to test a couple other cells in the row for values (col B,C,D)
Col B,C will have text or blank - no problem there
Col D may contain

1. blank (empty) or text

2. blank or text based on a formula

Situation 1 comes about when I COPY the data in the cells to remove the formula and have hard data.

How do I test for a blank returned by a formula?

Example

A3 contains =IF(ISBLANK(B3),IF(ISBLANK(C3),IF(ISBLANK(D3),"",A2),A2),A2)

D3 contains =IF(ISBLANK(E3),IF(ISBLANK(F3),"",D2),D2)

E3 ans F3 are BLANK, putting a BLANK into D3. But D3 has a formula so A3 does not see the BLANK

If a cell has content, no matter if numeric, text or formula, then it is not blank. You can test for an empty string, which yields also true for an empty cell. Furthermore you can simplify the expression in A3 to use less nested IF() calls,

``````=IF(AND(B3="";C3="";D3="");"";A2)
``````

`{=IF(COUNTIF(INDEX(B2:D5;ROW(A1:A4););"")=0;A1:A4;"")}` (e.g.) should also work, and
`{=INDEX(B2:D5;ROW(A1:A4);)}` actually returns the expected array.
@Lupp: Because COUNTIF() returns one scalar value, not an array of values; you can check with `=COUNTIF(INDEX(B2:D5;ROW(A1:A4););"")` entered as array formula. So the IF() returns either an array of A1:A4, or an empty string. Would be interesting what Excel does there…