Need help with iserror

[markaxtell]

Hi,

I was wondering if anyone could help with addding a iserror to the following

{=INDEX(Data!$D$1:$D$433,MATCH(C5,IF(Data!$C$1:$C$433=$F$4,Data!$A$1:$A$433),0))}

I have to press ctrl+shift+enter when entering the formula so will iserror work with this?

Many thanks in advance.

 

[Oeldere]

Have you tried this one already?

{=iserror(INDEX(Data!$D$1:$D$433,MATCH(C5,IF(Data!$C$1:$C$433=$F$4,Data!$A$1:$A$433),0)),"")}

 

[PhilW_34]

Hi Mark,

This should work for you.

=IF(ISERROR(INDEX(Data!$D$1:$D$433,MATCH(C5,IF(Data!$C$1:$C$433=$F$4,Data!$A$1:$A$433),0))),"",INDEX(Data!$D$1:$D$433,MATCH(C5,IF(Data!$C$1:$C$433=$F$4,Data!$A$1:$A$433),0)))

Paste the formula in your desired cell and then do your array formula key strokes.

Phil

 

[markaxtell]

Hi,

thanks for quick reply, it says that entered too many arguements for this function?

 

[markaxtell]

You are my hero!!!!!!!!

hi mark,

this should work for you.

=if(iserror(index(data!$d$1:$d$433,match(c5,if(data!$c$1:$c$433=$f$4,data!$a$1:$a$433),0))),"",index(data!$d$1:$d$433,match(c5,if(data!$c$1:$c$433=$f$4,data!$a$1:$a$433),0)))

paste the formula in your desired cell and then do your array formula key strokes.

Phil

 

[Scott Huish]

Hi, welcome to the board. Not usually too much need for ISERROR though. I shortened your formula as well.

=IF(ISNUMBER(MATCH($C$5&"|"&$F$4,Data!$A$1:$A$433&"|"&Data!C1:C433,0)),INDEX(Data!$D$1:$D$433,MATCH($C$5&"|"&$F$4,Data!$A$1:$A$433&"|"&Data!C1:C433,0)),"")

Confirm with CTRL-SHIFT-ENTER.

 

[markaxtell]

Hi, welcome to the board. Not usually too much need for ISERROR though. I shortened your formula as well.

=IF(ISNUMBER(MATCH($C$5&"|"&$F$4,Data!$A$1:$A$433&"|"&Data!C1:C433,0)),INDEX(Data!$D$1:$D$433,MATCH($C$5&"|"&$F$4,Data!$A$1:$A$433&"|"&Data!C1:C433,0)),"")

Confirm with CTRL-SHIFT-ENTER.

That one dont seem to work when i try and copy it down, i have removed the $ from the $C$5 colum

 

[Scott Huish]

Sorry, put absolute references on the C1:C433 parts.

 

[markaxtell]

Thank you that worked !

 

[markaxtell]

sorry last question if i want to me it all of row C instead of C1:C433 how would i change the formula?

=IF(ISNUMBER(MATCH(C5&"|"&$F$4,Data!$A$1:$A$433&"|"&Data!$C$1:$C$433,0)),INDEX(Data!$D$1:$D$433,MATCH(C5&"|"&$F$4,Data!$A$1:$A$433&"|"&Data!C$1$:$C$433,0)),"")