Index, Match

[Guest]

=IF(A25=9,"",INDEX(CLM10000!$A$1:$R$640,MATCH(Sheet1!H25,CLM10000!$B$1:$B$640,0),MATCH(Sheet1!$BX$6,CLM10000!$A$1:$R$1,0)))

Normally this works very well. The one problem I have experienced is when
the Index, Match portion references an empty cell. What do I need to add to
have this situation return "0". This way (my hope is) that subsequent
calculations won't return #value! errors. I understand =IF(A25=9,"" will
return a blank cell, however if this is the case then that cell would not be
used in any subsequent calculations.

Thanks Again!

M.A.Tyler

 

[JE McGimpsey]

Not sure what you're looking for. If Index() references an empty cell,
the value returned *is* 0.

 

[Guest]

In this case index returns "", when it references an empty cell. Is it
possible for it to return "0"? Or is it possible for the empty cell to be
used in subsequent calculations? Currently if I use the empty cell the next
mathmatical formula returns "#value!"

 

[JE McGimpsey]

Then your empty cell isn't really empty - it contains a null string. In
which case you could wrap the INDEX(...) with SUM(), since it ignores
text:

=IF(A25=9,"",SUM(INDEX(...)))

 

[Guest]

From playing around, it appears Index will return 0 when it references a cell
that is truly empty. Therefore, I suspect your Index function is referencing
a cell that contains the null string (ie "" - perhaps the result of an If
function?).

If so, another thing you could consider is to modify your functions that are
upstream of your Index function to return 0 instead of "" and change the
number format to custom and use something like

#,###;-#,###;;

so that 0 values will appear blank, but the cell value is still 0 for the
benefit of your dependent fomulae.

Perhaps not the quickest fix for your current situation, but something to
keep in mind for future projects.