#NUM! error in Array Formula - trying to use IFERROR to fix but not working

[LauraWork]

Hi all

can anyone help:

Heres my original formula for an array lookup and its working fine but I get #NUM! errors when it cant find any more matches so I found a IFERROR formula modified it to fit mine but it doesnt work: where have I gone wrong - I am entering it as an array CTRL SHIFT ENTER:

original
=INDEX(Lookup!$B$1:$F$6499,SMALL(IF(Lookup!$B$1:$B$6499=$A3,ROW(Lookup!$B$1:$B$6499)),ROW($A$1:$M$1)),5)

New
=IFERROR (INDEX(Lookup!$B$1:$F$6499,SMALL(IF(Lookup!$B$1:$B$6499="$A2",ROW(Lookup!$B$1:$B$6499)),ROW($A$1:$M$1)),5),"")

thanks
Laura

 

[Special-K99]

Youve changed A3 to A2 - why?

 

[LauraWork]

Sorry my typo: the formula rus down in column B then the 2nd instance in C (looking up row 2) and so on:
Row 2 formula is =INDEX(Lookup!$B$1:$F$6499,SMALL(IF(Lookup!$B$1:$B$6499=$A2,ROW(Lookup!$B$1:$B$6499)),ROW($A$1:$M$1)),5)
the row 3 formula =INDEX(Lookup!$B$1:$F$6499,SMALL(IF(Lookup!$B$1:$B$6499=$A3,ROW(Lookup!$B$1:$B$6499)),ROW($A$1:$M$1)),5)
etc

 

[Matty]

Hi,

Can you provide a small example of your data as well as an explanation of what you want the formula to deliver?

Matty

 

[Peter_SSs]

original
=INDEX(Lookup!$B$1:$F$6499,SMALL(IF(Lookup!$B$1:$B$6499=$A3,ROW(Lookup!$B$1:$B$6499)),ROW($A$1:$M$1)),5)

New
=IFERROR (INDEX(Lookup!$B$1:$F$6499,SMALL(IF(Lookup!$B$1:$B$6499="$A2",ROW(Lookup!$B$1:$B$6499)),ROW($A$1:$M$1)),5),"")

How does it go if you remove those red double quotes?

 

[LauraWork]

Thanks Peter that works - school girl error!!
sometimes I cant see the wood for the trees!

 

[Aladin Akyurek]

Thanks Peter that works - school girl error!!
sometimes I cant see the wood for the trees!

Where do you enter this formula

=IFERROR (INDEX(Lookup!$B$1:$F$6499,SMALL(IF(Lookup!$B$1:$B$6499=$A2,ROW(Lookup!$B$1:$B$6499)),ROW($A$1:$M$1)),5),"")

and in which direction are you copying it?