# Return a column value based on looking at 3 other columns

I have a pivot table that has 3 columns with values. I need to perform a search of these columns and return a value from another column. The issue is this - I need excel to look at the 3 columns and if the 1st column has data, I need it to see the that it has a value, and if the other two have a value to not return the value from the other column. Example, if:

`````` | A  | B  | C  | D  | Answer
-+----+----+----+----+--------
1| 1  | 1  | 1  | X  |
``````

No value should be returned from the "Answer Column".

If

`````` | A  | B  | C  | D  | Answer
-+----+----+----+----+--------
1|    | 1  | 1  | X  |
``````

Then value returned from "Answer Column" (D1 = "John Smith").

Next, if any of the other column cells have a BLANK value then no value is returned from the "Answer Column".

I have attempted this with nested `IF(OR)` statements and tried using `ISBLANK` as well, with no success.

`=IF(ISBLANK(A1),D1,NA())`

This is a very simple formula checking if A1 is blank if not it gives #N/A, you may replace #N/A with "". This formula works for the 2 test cases you presented as I understood them.

• This doesn't work in the first case. If A1, B1, C1 = 1, 1, 1 respectively, your formula gives the value from D1 while it should give blank. – Jerry Feb 8 '14 at 8:11

You can use the formula:

``````=IF(AND(ISBLANK(A1),COUNTA(B1:C1)=2),D1,"")
``````

This checks first if A1 is blank and that B and C are not. If both are true, return the answer from column D, otherwise, return a blank.

• That was what I needed. I just couldn't see it as I was hanging up on the ISBLANK! I appreciate the help and quick response. – Dave Sep 24 '13 at 12:38