Converting errors and zeros to blanks in nested INDEX,MATCH & COUNTIF formula

[viper1953]

Hi,

I have the following formula in 20 cells in Column A which references values in column V in a sheet called December 2014:

=INDEX('DECEMBER 2014'!$V$2:$V$20,MATCH(0,INDEX(COUNTIF($A$1:A1,'DECEMBER 2014'!$V$2:$V$20),0,0),0))

It gives the following results:

United Kingdom
United States
New Zealand
0
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A

In order to conditionally format non blank cells containing country names I wish to convert the #N/A and 0 to blank cells by incorporating it into the formula. I realise I can change the format of the cells to 0;-0;;@ but I'd much rather do it in a formula.

I have also successfully managed to use IF(ISERROR......) in another formula but not in combination with the zeros.

Any help greatly appreciated as it's been driving me nuts!

Cheers

Mark

[mcmahobt]

Would something like this work?

Please Login or Register  to view this content.

I'm sure someone else could think of a more concise formula, but this would be a quick and dirty approach.

Hope this helps!

[Jonmo1]

Try

=IFERROR(T(INDEX('DECEMBER 2014'!$V$2:$V$20,MATCH(0,INDEX(COUNTIF($A$1:A1,'DECEMBER 2014'!$V$2:$V$20),0,0),0))),"")

[viper1953]

That is great, thanks guys. Both options worked so have chosen the latter.

There might be a couple of others but i'll try and fathom it out before asking!

Cheers!

[Jonmo1]

You're welcome.

Note on my formula, the T function is what's hiding the 0, but it will only work here if the 'Expected' Result of the Index/Match is a TEXT value.
If the result of the Index Match is any number other than 0, it will be hidden as well.
But your example appears to be returning just Text Strings.

[viper1953]

Yeah, I've just tried the following with results as you describe - it doesn't work as the expected value is a number:

=IFERROR(T(COUNTIF('DECEMBER 2014'!$V$2:$V$7,A2)),"")

Is there, by any chance, a numerical equivalent to the 'T' function?

[Jonmo1]

For that you'd have to go with mcmahobt's method.

But I'd use IFERROR instead of IF(ISERROR

=IFERRROR(IF(COUNTIF(....)=0,"",COUNTIF(...)),"")

[viper1953]

I've just managed to sort it by using:

=IF(COUNTIF('DECEMBER 2014'!$B$2:$B$7,C2)=0,"",COUNTIF('DECEMBER 2014'!$B$2:$B$7,C2))

I didn't need the IFERROR as the result would never be in error - just 0 upwards.

It looks really simple and logical now it is working!

Huge thanks for the help and pointers, much appreciated.

Mark

[Jonmo1]

You're welcome.