INDEX MATCH error

**Ozwilly** · 10-07-2012, 09:17 PM

Hi,

I have a formula I have been using whereby it checks a column on a reference tab, and if that cell contains a text value, it returns that text value. What I want to have happen however is that if there is no text value in the reference column, it returns a blank (""). The problem is that instead of returning a blank, it is returning '0' for cells with no text entry in the reference column. I have tried to alter the formula below so that instead of '0' in the index match, I use "") but get the #VALUE! error. On cells that actually do have a text entry in the reference cell, using this formula with "" instead of '0' returns a blank! I have done all the usual, text to column on all reference columns etc. Appreciate any help!

=IFERROR(INDEX(Categorisation!F:F,MATCH(Data!Y61,Categorisation!G:G,0)),"")

**vlady** · 10-07-2012, 09:52 PM

maybe you can adopt the if(isna
or if statement alone.

=if(isna(index...match)),"",index(..match))

or

if(index(...match))="","",index(..match)) - adjust if needed for the if statement

**Ozwilly** · 10-07-2012, 10:23 PM

Thanks, tried and could not get the ISNA to work, so tried the IF on its own. Works fine for cells that are meant to be blank i.e. now pprovides a blank instead of '0', however is also blanking out cells that are meant to contain a text value as it was before

**Ozwilly** · 10-07-2012, 10:25 PM

Think I may have just got it to work thanks, by using the =0 in the end of the first index match, and it then provides a blank!

=IF(INDEX(Categorisation!F:F,MATCH(Data!Y4,Categorisation!G:G,0))=0,"",(INDEX(Categorisation!F:F,MATCH(Data!Y4,Categorisation!G:G,0))))

Much appreciated!