Thread: Index/Match to return a blank cell

Please can someone make an adjustment to my Formula so that if the Index match locates a cell that is blank it returns a blank cell rather than a 0. I know we need to add in =IF(iserror( but i am aving trouble..

=INDEX('Information '!$A$1:$BF$87,2,(MATCH(Summary!$A$23,'Information '!$A$1:$BF$1,0)))

Thanks

Nick, rather than double evaluating the formula you may find that using a Custom Format on the cell containing the formula itself will suffice... (?)

[=0]"";General

(change General to be whatever you would use normally)

If 0 comes back via the INDEX formula the above custom format will format the cell such that it "appears" blank
(0 value persists in cell)

Else as I say you need to double evaluate the formula which pending volume of calculations may be best avoided...

You can of course alter your "other" formulae so that instead of testing to see if value in cell = "" you test to see if = 0.

If you really are determined to double evaluate...

The board software inserted a space into Sweep's formula when listing "information" and I think his sheet references are missing the trailing space
(ie Information! as opposed to 'Information '!) ... if you alter references accordingly it should work ok.

A (very) slight alternative to sweeps

=IF(INDEX('Information '!$A$2:$BF$2,MATCH(Summary!$A$23,'Information '!$A$1:$BF$1,0))="","",INDEX('Information '!$A$2:$BF$2,MATCH(Summary!$A$23,'Information '!$A$1:$BF$1,0)))

In the above given you're returning a fixed row (2) you can simply set that row as your reference range -- no need to reference other rows.

I would still advise you update your other formulae and use formatting given you're not adding any additional tests to slow your other formulae down you are merely altering the conditional test from "" to 0.

Your choice though obviously and you should do whichever you think makes most sense in your situation.

Cheers

Dear friends, i would like to request your help once more. I have a similar problem as Nick stated above, need to have a match/index formula to show blank when the source cell is blank as well. I have tried the solution given but unfortunately it does not work. Can you please tell me what error i am making?

=IF(INDEX(SHEET1!$C$3:$C$400;MATCH(A19;SHEET1!$A$3:$A$400;0))="";"";INDEX(SHEET1!$C$3:$C$400;MATCH(A 19;SHEET1!$A$3:$A$400;0)))


*I have the 2007 Excel Version

Good morning Maria.

..This is a 3 years Old Thread.!! How did you found it??

There is a space in your formula after second MATCH...Maybe is this...

=IF(INDEX(SHEET1!$C$3:$C$400;MATCH(A19;SHEET1!$A$3:$A$400;0))="";"";INDEX(SHEET1!$C$3:$C$400;MATCH(A 19;SHEET1!$A$3:$A$400;0)))

Maria, would you like to upload a sample workbook??

I am sure we'll find the solution.

(Χαίρομαι πολύ όταν συναντώ Έλληνες στο φόρουμ!!)

If the return values are text rather than numbers, you can simply append ""

=INDEX(SHEET1!$C$3:$C$400;MATCH(A19;SHEET1!$A$3:$A$400;0))&""

Ηι

Another way is using, this formula, in Sheet!2 C13, for example..

=IF(ISERROR(C13);"";(INDEX(Sheet1!$C$3:$C$400;MATCH(A19;Sheet1!$A$3:$A$400;0))))

This formula, will gives you a cycle referance. To avoid this, do this.

Tools>>Options>>Calculation>Maximum Iteration, must be 1! Not 100

Then each time that a #NA, will appears, you just click the save Button, or type something enywhere in the Sheet.

Hope to helps you.

For future reference, it helps if you can be more specific than "it does not work". We like to know whether you get an error (and if so, which one), or incorrect results, or...