Excel not showing blank in formula when I want it to

[Jyggalag]

Hi all,

I currently have this formula:

Right now the cell is showing a blank cell.

I then have another cell that links to this cell through this formula:

This cell also shows blank, because the first cell is blank.

I then have a third and final cell with this formula:

However, this cell returns the value "Not blank", which it should not, because the other two cells are both blank (specifically the second one, which this one links to).

Can somebody please help me out on how to fix this?

Thank you so much!

Best regards,
Jyggalag

 

[Tupe77]

The upper formula is correct and the lower one is for you, but since your formula was pictured, it may require correction.

=IFERROR(IF(INDEX($E$2:$E$10,MATCH(K1,$D$2:$D$10,0),1)="","",INDEX($E$2:$E$10,MATCH(K1,$D$2:$D$10,0),1)),"")

=IFERROR(IF(INDEX('Master data 2019'!$C$2:$FL$35,MATCH(IRL!$C$6,('Master data 2019'!$B$2:$B$35,0),IRL!G12)="","",INDEX('Master data 2019'!$C$2:$FL$35,MATCH(IRL!$C$6,('Master data 2019'!$B$2:$B$35,0)),"")

So if I finally understood what you wanted .....

 

[Jyggalag]

The upper formula is correct and the lower one is for you, but since your formula was pictured, it may require correction.

=IFERROR(IF(INDEX($E$2:$E$10,MATCH(K1,$D$2:$D$10,0),1)="","",INDEX($E$2:$E$10,MATCH(K1,$D$2:$D$10,0),1)),"")

=IFERROR(IF(INDEX('Master data 2019'!$C$2:$FL$35,MATCH(IRL!$C$6,('Master data 2019'!$B$2:$B$35,0),IRL!G12)="","",INDEX('Master data 2019'!$C$2:$FL$35,MATCH(IRL!$C$6,('Master data 2019'!$B$2:$B$35,0)),"")

So if I finally understood what you wanted .....

It does not seem like the formula works

 

[Tupe77]

Maybe this works?

=IFERROR(IF(INDEX('Master data 2019'!$C$2:$FL$35;MATCH(IRL!$C$6;'Master data 2019'!$B$2:$B$35;0);IRL!G12)="";"";INDEX('Master data 2019'!$C$2:$FL$35;MATCH(IRL!$C$6;'Master data 2019'!$B$2:$B$35;0)));"")

The pictures always help to outline the situation, but put the formulas as formulas, so we don't have to rewrite them every time, Please!

 

[Jyggalag]

Maybe this works?

=IFERROR(IF(INDEX('Master data 2019'!$C$2:$FL$35;MATCH(IRL!$C$6;'Master data 2019'!$B$2:$B$35;0);IRL!G12)="";"";INDEX('Master data 2019'!$C$2:$FL$35;MATCH(IRL!$C$6;'Master data 2019'!$B$2:$B$35;0)));"")

The pictures always help to outline the situation, but put the formulas as formulas, so we don't have to rewrite them every time, Please!

Hi!

I actually solved it through the 3rd formula (the IF formula). It might not be the best solution, but it works for now

=IF(code(cell name)=48;"BLANK";"NOT BLANK"). Because a blank cell with value 0 has code value 48

 

[MARK858]

Because a blank cell with value 0 has code value 48

For info a cell that is a truly blank cell can't have a value of 0 and return 48 with the CODE function , to be 0 it is either returning 0 or FALSE.

If a cell is returning "" from a formula then that is an empty string and not a truly Blank cell, which is why you test for it using either IF(?? ="","XX","YY") or =IF(LEN(??) = 0,"XX","YY") rather than =ISBLANK(??)