Index/match - make blank cells return a blank value.

[diaare]

I have the following formula:

=INDEX(Summary!$E$2:$E$79,MATCH(VendorCommodities_Detail!$C3,Summary!$C$2:$C$79,0))

It caluculates correctly. But, I am working with a column that tracks
percent changes. Right now if the column is blank (I have yet to put a
percent in it) the formula reutrns a 0%. But, many times there is no change
in percent, and the 0% is truely accurate. I need to be able to distinguish
the dirrerence between the actual 0% and the one that says it is 0% due to no
value being entered into the index cell.

My intitial thought is make it return a blank cell but I am not sure how,
and I am not sure if thoses blank cells will mess up other caluculations
(sums, avgs, etc) that calculate off those cells.

What is the best way to go about this?

Thanks,
Diane

 

[Peo Sjoblom]

AVERAGE and SUM ignore text (a "" is text) so you can use blanks

=IF(INDEX(B1:B4,MATCH(D1,A1:A4,0))="","",INDEX(B1:B4,MATCH(D1,A1:A4,0)))


adapt to fit your sheet names and ranges

 

[diaare]

Perfect.

Thanks

AVERAGE and SUM ignore text (a "" is text) so you can use blanks

=IF(INDEX(B1:B4,MATCH(D1,A1:A4,0))="","",INDEX(B1:B4,MATCH(D1,A1:A4,0)))


adapt to fit your sheet names and ranges