Lookup data in different sheets with duplicate data

Hi all,

I have a list of products (sheet 1) that I need to find the locations for goods picking, the master inventory list (sheet 2) has the same product code but with different location, what function should I use so that my (sheet 1) can view all possible location. Example as follows:

(sheet 1)
Product no Location
123.11
124.12
123.23

(sheet 2)
Product no Location
123.11 A01
123.11 A02
123.23 B01
123.23 C01
123.23 D01
124.12 F01


Please help.

Thank you very much.

Hi, any example how I can do it? I mean when I key in the product code, the location can straight away pop up. thanks

Hi, it looks very complicated to me.

This is a sample, I need to find location for picking list using the data in the Master Inv list.

what if i am using excel 2010? i cant understand Alan code

Originally Posted by alansidman

I've loaded my code into the sheet you attached. It is an User Defined Function and can be called from the Function task bar. Look at the file attached.

Hi Guru,

Thank you, its the results that I want, but how do I install the UDF? For your kind advice.

Thank you.

Originally Posted by alansidman

Open your spreadsheet. Click on the Alt + F11 key. This opens the VBA window. Click on insert. Insert module. Paste my code in. Close and save the VBA window. In the spreadsheet, click on the Formula fx. Go to User Defined select Multi-vlookup.

Alan

Hi Alan,

Sorry for my knowledge in Excel, I tried to copied your code..( =VLookUpMulti(A2,'Master inv list'!$A$2:$B$11,2,,)) but it didnt seems to work for me... Need your kind advice. Thank you.