Help with Index/ISBLANK

[mango2015]

Hi,

I've got an issue with my formula and I am struggling to find solutions on the internet on how to do this correctly, would really appreciate some help.

A14 =IF(ISBLANK(Suppliers!B3:G21)," ",INDEX(Suppliers!F$3:F$187,MATCH($A$9,Suppliers!$A$3:$A$180,0)))

I use the formula to index and match data on another table, but the ISBLANK isn't working as I would like it too.

Is there a way I can change my formula so that if there is nothing in CleaningCo's Line5 for example instead of the formula returning with a zero it would return empty/blank. I know how to do it for a single cell but not with a range.

I am unsure if I have explained this very well, but if anyone could help that would be great, I've attached a copy of my spreadsheet.

Gus

[sandy666]

If I understand well:
=IF(ISBLANK(Suppliers!B3:G7)," ",IF(INDEX(Suppliers!F$3:F$270,MATCH($A$9,Suppliers!$A$3:$A$200,0))=0,"",INDEX(Suppliers!F$3:F$270,MATCH($A$9,Suppliers!$A$3:$A$200,0))))

[Pepe Le Mokko]

Don't exactly understand what you are trying to do but to check if a range is empty something like =IF(COUNTBLANK(C3:G3)=COLUMNS(C3:G3),..... should help

[sabusby]

You can also employ COUNTA, which counts cells that are NOT blank.

[mango2015]

If I understand well:
=IF(ISBLANK(Suppliers!B3:G7)," ",IF(INDEX(Suppliers!F$3:F$270,MATCH($A$9,Suppliers!$A$3:$A$200,0))=0,"",INDEX(Suppliers!F$3:F$270,MATCH($A$9,Suppliers!$A$3:$A$200,0))))

Thank you! That's exactly what I was trying to do, I did come across a thread similar to this one, but I did not know how to apply the code someone suggested to my formula. Thanks again.

Don't exactly understand what you are trying to do but to check if a range is empty something like =IF(COUNTBLANK(C3:G3)=COLUMNS(C3:G3),..... should help

Thank you also for the reply, I am still fairly new to formulas in excel, I manage to find most answers on google but I was struggling a bit with this one.

[sandy666]

Glad to help.
If problem is resolved mark thread as solved. Thank you

[Pepe Le Mokko]

FYI COUNTBLANK counts "real" empty cells and cells seemingly blank ( containing the null text string "")

ISBLANK does not recognize "" as an empty cell

[skaffapingvin]

Hi,

I also have a similar problem. Below is the original formula:
CELL L8 =INDEX('OCAS OCIS'!$R$2:$R$3232,MATCH('6 Short Recipes'!$C8,'OCAS OCIS'!$A$2:$A$3232,0))

IF Cell K8 is blank, execute the above formula =INDEX('OCAS OCIS'!$R$2:$R$3232,MATCH('6 Short Recipes'!$C8,'OCAS OCIS'!$A$2:$A$3232,0))
but if cell K8 is not blank change the write "O" in cell L8.

I have tried this formula but it doesn't work, what am I doing wrong?
Cell L8 =IF(ISBLANK($K8),INDEX('OCAS OCIS'!$R$2:$R$3232,MATCH('6 Short Recipes'!$C8,'OCAS OCIS'!$A$2:$A$3232,0)),"O")

[sandy666]

@skaffapingvin

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.