Index and Small Formulas returning #N/A

Hi,

I'm having trouble using the INDEX formula in this worksheet: Vials_Index.xlsx

Basically I want to return the corresponding supplier batch and location for each instance of a certain catalogue number. As there is likely to be more than one instance of each catalogue number on the spreadsheet, I know I can't use a VLOOKUP, so I googled and followed the instructions for setting up an INDEX formula instead. I got this to work perfectly on a simple dummy run (sheet 2), but when I tried it out on the main sheet, all I got was an #N/A value.

I can't work out why this is and I'm not really familiar with INDEX or SMALL at all, and frankly my brain hurts now Can anyone shed some light on this please??

Hi - I think there are a couple of problems:
-I'm not sure you can use entire column references (and even if you can, it is better not to, it will save on computational time)
-If your dataset has errors in, this will cause it to trip up.

Try something like:
=SMALL(IF(ISERROR(D$3:D$5000),100000,IF(D$3:D$5000=D3,ROW(D$3:D$5000),100000)),ROW(1:1))

Thanks for the advice - I removed all the #N/A values on the worksheet that represented empty spaces, and this solved the problem
I'll maybe use a range instead of a whole column reference as well.

Thanks again