ISBLANK formula doesn't work because the cell has a Lookup formula

[tropics123]

Hi guys, I ran into a roadblock and was hoping someone could shed some light. Row 1 is the header. There is currently lookup formulas in all cells starting on C2. My formula in G2 is basically looking if C2 is blank, then move on to pull in a value from C2 and if C2 is blank then move onto D2, and so on. However, the issue is, those cells even though they look like blank, there's a lookup formula in them so my formula doesn't work. Is there a formula that can do what I'm doing but ignore the lookup formula in those cells and still consider them as blank? And all cells return blank then put in value "N/A". Thank you in advance!

Here is my formula:
=IF(ISBLANK(C2),IF(ISBLANK(D2),IF(ISBLANK(E2),IF(ISBLANK(F2),"N/A",F2),E2),D2),C2

Column A	Column B	Column C	Column D	Column E	Column F	Column G
NAME	ADDRESS	DATA 1	DATA 2	DATA 3	DATA 4	Final Answer
						=IF(ISBLANK(C5),IF(ISBLANK(D5),IF(ISBLANK(E5),IF(ISBLANK(F5),"N/A",F5),E5),D5),C5)

 

[DanteAmor]

Try this:

=IF(C2="",IF(D2="",IF(E2="",IF(F2="","N/A",F2),E2),D2),C2)

 

[joeu2004]

The point is: ISBLANK [sic] is a misnomer. It should have been called ISEMPTY. It returns TRUE only when a cell have no value. That is, it has no constant value, and it has no formula.

So it is not that ISBLANK "doesn't work". It is just that you misunderstood how it works, quite understandably.

FYI, appearances can be deceiving. If you enter the formula ="" into a cell, then copy-and-paste-value the same cell, it appears to be empty. But note that ISTEXT returns TRUE, indicating that there is indeed a (text) value.

 

[tropics123]

Try this:

=IF(C2="",IF(D2="",IF(E2="",IF(F2="","N/A",F2),E2),D2),C2)

Hi DanteAmor, your suggested formula worked, except for the part where it needs to put in "N/A" when the cells are all blank. For example, cells C2, D2, E2, F2 are all blank, then there should be N/A, but instead it shows blank for the answer. Any suggestions? Thank you

 

[DanteAmor]

you can put here the formulas you have in c2, d2, e2 and f2

maybe some returns spaces instead of ""

 

[tropics123]

you can put here the formulas you have in c2, d2, e2 and f2

maybe some returns spaces instead of ""

Hi DanteAmor, here are the formulas. Thank you for looking at this!

C: =IFERROR(INDEX(Database1!$1:$1048576,MATCH('Combined Database'!$A$1,Database1!$K:$K,0),MATCH('Combined Database'!$B346,Database1!$A$1:$IU$1,0)),"")

D: =IFERROR(INDEX(Database2!$1:$1048576,MATCH('Combined Database'!$A$1,Database2!$K:$K,0),MATCH('Combined Database'!$B346,Database2!$A$1:$IU$1,0)),"") E: =IFERROR(INDEX(Database3!$1:$1048576,MATCH('Combined Database'!$A$1,Database3!$K:$K,0),MATCH('Combined Database'!$B346,Database3!$A$1:$IU$1,0)),"") F: =IFERROR(INDEX(Database4!$1:$1048576,MATCH('Combined Database'!$A$1,Database4!$K:$K,0),MATCH('Combined Database'!$B346,Database4!$A$1:$IU$1,0)),"") G: =IF($C346="",IF($D346="",IF($E346="",IF($F346="","N/A",$F346),$E346),$D346),$C346)

 

[DanteAmor]

The result of some of the formulas is not empty ("") is returning a blank space.
Copy the cells and paste elsewhere but as values, edit each cell and check which one has a blank space.

 

[tropics123]

The result of some of the formulas is not empty ("") is returning a blank space.
Copy the cells and paste elsewhere but as values, edit each cell and check which one has a blank space.

Thank you for the tip! I added TRIM to all the formulas and that took care of the issue. I really appreciate your help!!!

 

[tropics123]

Actually, the TRIM formula removed the spaces but for the cells with date, it removed the date format (even if I changed the cells to date format). 1/12/1980 would look something like 43385.

 

[DanteAmor]

How about:

=IFERROR(TRIM(INDEX(Database1!$1:$1048576,MATCH('Combined Database'!$A$1,Database1!$K:$K,0),MATCH('Combined Database'!$B346,Database1!$A$1:$IU$1,0)))+0,"")