On 26 Jun 2005 10:07:06 -0700, "gordo" wrote:

My appologies. i have tried the formula below which has some success i
still have a problem when the SKU dosent match it is bringing the next
location down even when this is attached to a different SKU.
=IF(ISERROR(INDEX(Backup!$C$2:$H$3000,SMALL(IF($A 4=Backup!$C$2:$C$3000,ROW(Backup!$H$2:$H$3000)),RO W($1:$1)),1)),"",INDEX(Backup!$H$2:$H$3000,SMALL(I F(Backup!$C$2:$C$3000=$A4,ROW(Backup!$C$2:$C$3000) ),ROW($1:$1)),1))
Can you please advise

I would use, as I wrote previously, a different approach.

I am assuming now that your SKU is in Backup!$C$2:$C$3000 and your Loc's are in
Backup!$H$2:$H$3000.

For now, to see how it works, put the SKU for which you are looking, and the
instance number of the Loc you wish to match, in A1 and A2 respectively.

Then try this formula:

=INDEX(Backup!$H$2:$H$3000,LARGE((A1=Backup!$C$2:$ C$3000)*
ROW(Backup!$C$2:$C$3000),COUNTIF(Backup!$C$2:$C$30 00,A1)-A2+1)-1)


--ron