Index Error Pulling incorrect date

[yesitskez]

Hi Guys,

I could do with some assistance with this error, I have attached the file which shows the issue I have. In Sheet2, column B, Row 22, pulls in a date of 29/5/2013, when you look in Sheet1 it should be pulling in 28/5/2013 ?, I had help with this formula below and cannot understand why it is doing this ?
=IFERROR(INDEX(Sheet1!$B$2:$C$300,SMALL(IF(Sheet1!$C$2:$C$300=$A$1,ROW(Sheet1!$C$2:$C$300)),ROW(Sheet1!20:20)),1),"")

Thanks.

[Jonmo1]

Your INDEX is beginning in Row 2.

The Row# of an index is not strictly a Row #, it's the Row Position # relative to the Indexed range.
So if your indexing A2:A10, then the 2nd index row# is actually going to be A3..NOT A2.

If the result of your SMALL IF pulls the Row # From C2:C300, say the smallest is in C5, then the small if returns 5.
But the INDEX #5 of B2:B300 is actually B6 NOT B5

Try including row 1 in the INDEX and ROW functions
=IFERROR(INDEX(Sheet1!$B$1:$C$300,SMALL(IF(Sheet1!$C$1:$C$300=$A$1,ROW(Sheet1!$C$1:$C$300)),ROW(Sheet1!20:20)),1),"")

[JieJenn]

Why not use Advanced Filter?

[yesitskez]

Thanks Jonmo1, I never noticed the '1' was missing.

[yesitskez]

Thanks Jiejenn, I will look into the advanced filter.

[Jonmo1]

Great, glad to help.

[Tony Valko]

Try this...

Array entered** in B3:

=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(Sheet1!C$2:C$300=A$1,ROW(Sheet1!C$2:C$300)),ROWS(B$3:B3))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.