# array formula results with empty cells

#### Kmitchell

##### Active Member
I need assistance modifying the below array formula. I want to add that if the results return "" to make it "". Currently if a result is blank its returning a 0.

IF(F2="","",(INDEX('MOD LOOKUP'!\$M\$2:\$M\$176,MATCH(1,('Billed Unit Details'!\$F2='MOD LOOKUP'!\$L\$2:\$L\$176)*('Billed Unit Details'!\$I2='MOD LOOKUP'!\$K\$2:\$K\$176),0))))

I tried to modify the array as shown below but it returns #VALUE! in the instances where the cell should be "".

=IF(F3="","",(INDEX('MOD LOOKUP'!\$M\$2:\$M\$176,MATCH(1,('Billed Unit Details'!\$F3='MOD LOOKUP'!\$L\$2:\$L\$176)*('Billed Unit Details'!\$I3='MOD LOOKUP'!\$K\$2:\$K\$176),0)="","",IF(F3="","",(INDEX('MOD LOOKUP'!\$M\$2:\$M\$176,MATCH(1,('Billed Unit Details'!\$F3='MOD LOOKUP'!\$L\$2:\$L\$176)*('Billed Unit Details'!\$I3='MOD LOOKUP'!\$K\$2:\$K\$176),0)))))))

Any suggestions?

### 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.
IF(F2="","",(INDEX('MOD LOOKUP'!\$M\$2:\$M\$176,MATCH(1,('Billed Unit Details'!\$F2='MOD LOOKUP'!\$L\$2:\$L\$176)*('Billed Unit Details'!\$I2='MOD LOOKUP'!\$K\$2:\$K\$176),"to make it"))))

try that

Thank you! That did not give expected results, but I was successful with this

=IF(IF(F399="","",(INDEX('MOD LOOKUP'!\$M\$2:\$M\$176,MATCH(1,('Billed Unit Details'!\$F399='MOD LOOKUP'!\$L\$2:\$L\$176)*('Billed Unit Details'!\$I399='MOD LOOKUP'!\$K\$2:\$K\$176),0))))=0,"",IF(F399="","",(INDEX('MOD LOOKUP'!\$M\$2:\$M\$176,MATCH(1,('Billed Unit Details'!\$F399='MOD LOOKUP'!\$L\$2:\$L\$176)*('Billed Unit Details'!\$I399='MOD LOOKUP'!\$K\$2:\$K\$176),0)))))

Replies
0
Views
523
Replies
1
Views
299
Replies
1
Views
432
Replies
2
Views
672
Replies
5
Views
1K

1,196,447
Messages
6,015,310
Members
441,887
Latest member
acquamarine

### 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?

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