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
  • @Dave You're welcome ^^ – Jerry Sep 24 '13 at 12:39

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.