Index/Match with multiple results

[amcghee1]

Hi folks.
I'm using this formula:
=INDEX('Sheet1 BV'!$A$1:$G$10000,MATCH($F$2,'Sheet1 BV'!$G$1:$G$10000,0),2)

to return lot numbers in column B from dates in column G, but some dates have multiple lot numbers in different rows (each lot number is in a row on it's own with a date, but sometimes a date will appear on more than one lot number). The formula i'm using obviously just returns the first result, but can anyone suggest how I may return all valid results in the column? I've tried searching around & while there are similar questions the answers don't seem to work out when I adjust them for my own application.

Any help is much appreciated,
Andy

[Fotis1991]

Maybe with an Array INDEX, but i need a sample workbook.

[amcghee1]

Hi Fotis.
Here is a stripped down version containing the relevant parts to keep it simple.

Thanks,
Andy

[Fotis1991]

In your example, which is the expected result and why?

[amcghee1]

Hi Fotis.
In that example, the yellow cell in the 'Converter' worksheet is the result, which is a lot number, based on cell F2 which contains the date. The formula looks up the date on the 'Sheet1 BV' worksheet & returns the lot number. However there may be several results & all I am getting is the first one at the moment.

The date I had in there may be unique, but others such as 11/05/2012 have more than one entries (3 I think for that one), & I need to return all of the corresponding lot numbers from column B for that date.

Cheers,
Andy