find and returning adjacent range of cell from another workbook

Hello VBA experts.

I have on simple query.

We have prepared one workbook which contains the details of our complete inventory. there will be about 20000 such rows and each part number may have changed once to up to 5 times. We have already updated the details of all the alternative part number in this file in the below given format.Part number is numeric and alpha numeric both

original Part number alternate Part number 1 alternate part number 2 alternate part number 2 and so on

abcd1 abcd2 abcd3 abcd4 and so on.

efgh efgh1

hjpk

We received part requirement of one part which is entered in some different worksheet. Say the part number is Abcd1. We maintain such records in a different workbook and then looks for the alternate part number in another worksheet manually so that the available part ( including the alternate part number) is given to the person.

My requirement.

We receive about 100-200 such part request and finding every time alternate part number manually in a different workbook is time consuming.

Is it possible if the required part number is entered in cell "A2" in one workbook then in cell "B2" till "F2" in the same workbook all the alternate part number is written automatically ( through formula or macro) after finding the same from a different workbook in which the details of all the alternate part number is already available as explained above.


Thanks in advance.

regards

RG