Need help to expand my look up range in a very long (and probably over complex) index formula with array

[saraapple]

This is my formula which works!

{=IFERROR(INDEX('3.PASTE PARAGON 2'!$B$2:$E$346,SMALL(IF('3.PASTE PARAGON 2'!$B$2:$E$346=$C2,ROW('3.PASTE PARAGON 2'!$B$2:$E$346)-1),COLUMNS($K2:K2)),4),"")}

It basically changes data from a column to a row in a new sheet.
Data changes every day so this is updated in the sheet '3.PASTE PARAGON 2' my problem is the data in this sheet can go beyond row 346 - I want it to capture the whole sheet but when I change the B2:E346 to B:E the formula stops working.

I am currently pulling it apart to try and figure out where I have gone wrong and wondered if it was something simple I am not seeing?

Any help gratefully appreciated.
Sara

 

[Fluff]

How about

=IFERROR(INDEX('3.PASTE PARAGON 2'!$B$2:$E$1000,SMALL(IF('3.PASTE PARAGON 2'!$B$2:$E$1000=$C2,ROW('3.PASTE PARAGON 2'!$B$2:$E$1000)-1),COLUMNS($K2:K2)),4),"")

 

[saraapple]

Thank you for taking a look.
{=INDEX('3.PASTE PARAGON 2'!$B$2:$E$1000,SMALL(IF('3.PASTE PARAGON 2'!$B$2:$E$1000=$C2,ROW('3.PASTE PARAGON 2'!$B$2:$E$1000)-1),COLUMNS($K2:K2)),4)}
I took out the IFERROR and it gives me a #N/A

If I increase the E346 +1 its starts #N/A

The current data in my sheet ends at row 346 which has the be the reason - what if tomorrows data has 500 rows or less than 346 - this will cause my formula to fail?

 

[Fluff]

If you are getting a #N/A error, that would suggest you have that error in the original data.

 

[saraapple]

You are right - there was a #NA line in my data.
Added and IFERROR so all my formulas can run and not cause problems to row 1000.
Thank you for pointing me in the right direction

 

[Fluff]

You're welcome & thanks for the feedback.