Jyggalag

New Member
Joined
Mar 8, 2021
Messages
39
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi all,

I currently have this formula:

1616744515259.png


Right now the cell is showing a blank cell.

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

1616744550590.png


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

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

1616744590870.png


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

Board Regular
Joined
Nov 26, 2020
Messages
97
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The upper formula is correct and the lower one is for you, but since your formula was pictured, it may require correction.

Excel Formula:
=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)),"")

Excel Formula:
=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 .....
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Jyggalag

New Member
Joined
Mar 8, 2021
Messages
39
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
The upper formula is correct and the lower one is for you, but since your formula was pictured, it may require correction.

Excel Formula:
=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)),"")

Excel Formula:
=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 :(

1616755954286.png
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
97
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Maybe this works?

Excel Formula:
=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

New Member
Joined
Mar 8, 2021
Messages
39
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Maybe this works?

Excel Formula:
=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 :)
 
Solution

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,069
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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(??)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,258
Messages
5,641,178
Members
417,196
Latest member
Liziz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top