If Index Match Formula

[ir121973]

Hi, I wonder whether someone may be able to help me please.

I'm using the folllowing formula to copy values from one sheet to another within the same workbook.

=IF(ISNA(INDEX(AprDaysInMonth,MATCH($A8,ResourceSummaryNames,0))),0,INDEX(AprDaysInMonth,MATCH($A8,ResourceSummaryNames,0)))-IF(ISNA(INDEX(AprForecast,MATCH($A8,ResourceSummaryNames,0))),0,INDEX(AprForecast,MATCH($A8,ResourceSummaryNames,0)))

For info, rather than using cell references I'm using 'Named Ranges' which are:

AprDaysInMonth
ResourceSummaryNames
AprForecast

The formula works well except when cell A8 is blank where the result of the formula becomes zero.

Rather than displaying zero I'd like to display a blank cell, but I've been unable to get this to work.

I just wondered whether someone could possibly look at this please and offer some guidance on how I may be able to achieve this.

Many thanks and kind regards

Chris

 

[jai9]

Hi,

Try something like,

=IF((Your existing formula)=0,"",(Your existing formula))

Jai

 

[CodeNinja]

If you are just looking for the display result, you can simplify this.... just take the range you are using these cells in, highlight it, and hit control + 1 to bring up the format cells dialog. Click on Custom and in the inputbox below type: enter ;""; (semi colon followed by 2 double quotes followed by a semi colon). This will format that range to show a blank when the answer is 0. 0 will still be in the cell, but it will show a blank.

 

[Aladin Akyurek]

Try to post a very tiny sample along with the desired results.

 

[ir121973]

Hi @jai, @CodeNinja and Aladin Akyurek, thank you all very much for taking the time to reply to my post.

Using the solution that @jai provided as a base, I was able to adapt this to suit my purposes and it works great.

Many thanks and kind regards

Chris

 

[Aladin Akyurek]

Hi @jai, @CodeNinja and Aladin Akyurek, thank you all very much for taking the time to reply to my post.

Using the solution that @jai provided as a base, I was able to adapt this to suit my purposes and it works great.

Many thanks and kind regards

Chris

Define BigNum as referring to:

=9.99999999999999E+307

=LOOKUP(BigNum,CHOOSE({1,2},0,
    INDEX(AprDaysInMonth,MATCH($A8,ResourceSummaryNames,0))))-
 LOOKUP(BigNum,CHOOSE({1,2},0,
    INDEX(AprForecast,MATCH($A8,ResourceSummaryNames,0))))

Custom format the formula cell as e.g.:

[=0]"";General