Checking 2 columns of data, to report data NOT found.

[apexRaiden]

My goal: I have a list of entries that includes 2 types of data in it. I need to check each cell against 2 columns of data. If it's there, I want the result cell to be BLANK. If it's NOT there, I want it to report the first function's results.

Ultimately, I am trying to find missing information and showcase that missing information. The data in column A is mixed with cells with JUST TEXT and cells with JUST NUMBERS AND DOTS. MAYBE I'm going about it the wrong way? But I feel like I'm so close. Here is the formula I'm currently banging my head against:

=IF(ISERROR(VLOOKUP($A2,C:C,1,FALSE)),"",IF(ISERROR(VLOOKUP($A2,D:D,1,FALSE)),"",$A2))

I keep going back and forth of rewriting it and I seem to only be able to get it to work on finding MATCHES but can't get it to work on finding MISSING ones.

Example...

Column A, Row 1 has data.
Check ALL of column C if that data is in any of the cells. No?
Check ALL of column D if that data is in any of the cells. No?
Report Column A, Row 1 data.

Column A, Row 2 has data.
Check ALL of column C if that data is in any of the cells. Yes?
Stop formula. Report a blank cell.

Column A, Row 3 has data.
Check ALL of column C if that data is in any of the cells. No?
Check ALL of column D if that data is in any of the cells. Yes?
Stop formula. Report a blank cell.

Thanks for your time!

 

[apexRaiden]

Just commenting on the post to get it some more traction for the day.

 

[Fluff]

How about

=IF(COUNTIFS(C:C,A2),"",IF(COUNTIFS(D:D,A2),"",A2))

 

[apexRaiden]

Fluff... I want to give you the biggest hug ever... Thank you SO much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[apexRaiden]

How about

=IF(COUNTIFS(C:C,A2),"",IF(COUNTIFS(D:D,A2),"",A2))

Any explanation WHY this one works? As in why countifs instead of iferror or iserror?

 

[Fluff]

Countifs will return 0 if the value isn't in col C & that is treated as false in this context, otherwise it's treated as true.

 

[apexRaiden]

Countifs will return 0 if the value isn't in col C & that is treated as false in this context, otherwise it's treated as true.

So if i do want a TRUE statement on something NOT being there, I use countifs?

 

[Fluff]

No, countifs just counts how many times a value occurs in the range.