ISNA in array formula problem

[Ozwilly]

Hi,

I have an array formula I want to put ISNA into, in order to show a blank space if there is no numeric value (""). I can't make it work. The existing formula is:

{=INDEX('Market Samurai data'!$E:$E,MATCH(B$3&$B3&$C3,'Market Samurai data'!$A:$A&'Market Samurai data'!$B:$B&'Market Samurai data'!$C:$C,0),1))}


I have changed it to the following, but am getting an error saying there are too many arguments:

{=INDEX('Market Samurai data'!$E:$E,if(isna(MATCH(B$3&$B3&$C3,'Market Samurai data'!$A:$A&'Market Samurai data'!$B:$B&'Market Samurai data'!$C:$C,0),1)),"",MATCH(B$3&$B3&$C3,'Market Samurai data'!$A:$A&'Market Samurai $B:$B&'Market Samurai data'!$C:$C,0),1)))}

Appreciate any help

[JBeaucaire]

Try this:

=IF(ISNUMBER(MATCH(B$3&$B3&$C3,'Market Samurai data'!$A:$A&'Market Samurai data'!$B:$B&'Market Samurai data'!$C:$C,0)), INDEX('Market Samurai data'!$E:$E, MATCH(B$3&$B3&$C3,'Market Samurai data'!$A:$A&'Market Samurai'!$B:$B&'Market Samurai data'!$C:$C,0)), "")

[Ozwilly]

Thanks for your quick reply I still can't make it work. I have attached the file that I am trying to fix. What I am trying to do is to match the date shown in G3 and the word in B4 & url in C4 with the data in the 'Market Samurai' tab. If it returns a value this will be shown, however if not it will return a blank space (no N/A)

I assume I don't need to do this as an array formula?

Appreciate your help

[Azam Ali]

Hi ozwilly

i think you mistakenly use B3 twice in your match formula MATCH(B$3&$B3&$C3
further you could do though simple formula instead of an array
if you are using excel 2007 then you should use iferror instead of isna function

use the follwoing formula in which i replace B$3&B$3 with A$3&B$3

=IF(ISNA(INDEX('Market Samurai data'!$E:$E,MATCH($A3&$B3&$C3,INDEX('Market Samurai data'!$A:$A&'Market Samurai data'!$B:$B&'Market Samurai data'!$C:$C,0),0))),"",INDEX('Market Samurai data'!$E:$E,MATCH($A3&$B3&$C3,INDEX('Market Samurai data'!$A:$A&'Market Samurai data'!$B:$B&'Market Samurai data'!$C:$C,0),0)))

[Ozwilly]

Thanks but still can't make it work. It is cells b4, C4 & G3 that I am wanting to reference in the data set. I am using Excel 2007. I tried to use this formula and it gave me blank cells - and given the cells are conditionally formatted gave me a coloured cell even though there was no value in it. Is this formula correct to use IFERROR?

=IFERROR(INDEX('Market Samurai data'!$E:$E,MATCH(G$3&$B4&$C4,'Market Samurai data'!$A:$A&'Market Samurai data'!$B:$B&'Market Samurai data'!$C:$C,0),1),"")

[JBeaucaire]

for data like this I prefer to keep it simple. If I need to concatenate 3 values for each row of data to get a unique "key", then I do so in a new column to make matching simple. I've added that key column with formulas to the data sheet.

Then in G4, this formula, copied down/across:

=IF(ISNUMBER(MATCH(IF($B4="", $B3, $B4)&$C4&G$3,'Market Samurai data'!$I:$I,0)), INDEX('Market Samurai data'!$E:$E, MATCH(IF($B4="", $B3, $B4)&$C4&G$3,'Market Samurai data'!$I:$I,0)), "")


Notice that some of your matches require you to "test" whether the B value is blank or not....

[Ozwilly]

Fantastic thanks What a formula - if only I fully understood it Am not sure why you would reference B4, the B3? then B4....my curious mind at work!!!

Also I have conditional formatting so that any number greater than 10 is red, yet even though there is no number in the cells that are blank, it is still red. Not sure how to fix this in conditional formatting?

[JBeaucaire]

The sample wb I attached to my post did not reflect red cells.

[Ozwilly]

That is strange as mine shows us red boxes. I have conditional formatting on however would have thought that it would also appear on yours? For some reason where the formula is returning a nil value and leaving it as a blank space, it is still showing the cells as red (which is a conditional format set for when a value is greater than 10. I would have thought blank would not have registered a value and triggered the formatting?)

I have attached a Word doc showing the relevant section. This was taken from the file attached to my last post. I tried reopening your wb and it is showing exactly the same thing!

[JBeaucaire]

I can't see that CF, I only have Excel 2003 so when I open the file I can only see three CF rules. Apparently you have more.

So, what is your CF formula for red?

[Ozwilly]

Ah that explains it, as I built this in Excel 2007

The formula I have for red is Cell Value >10 Applied to: =$H$4:$L$15,$G$5:$G$15

Not sure if that helps..... have googled but can't find an answer as to why having a formula in there assumes a value, and specifically one greater than 10 which results in the CF kicking in....