Modifying "vloookup" function for a specific task. Help needed.

[Dolke]

Hi to all.

I have needs for specific functionality in Excel, which cant be accomplished with some built in functions and features, so Im guessing that I need to make specific macro or to add new functionality in the form of some plugin maybe. As I'm doing this for the 1st time, any suggestion and help is highly appreciated.

So here is the detailed explanation of what I would like to accomplish:

I have 2 different excel files, and they are even stored on a different PCs in my LAN.
One file has table of my products. Each product consists of the flowing columns: name of the product, its unique code, supplier name, retail prize.
On the other PC I have same table with the same column name, just different content.

What I would like to do is compare these to files, in order to look for the match and for that I use VLOOKUP function.
So function takes the product code in one file and searches the 2nd file for that same code. When it finds code matching it returns the supplier name from the 2nd file which is in the column next to product code. This works perfectly fine.

Problem is that I only get first match, and later on there could and most of the time there are other matches later on in the table.
Result is always returned in the first file in one cell so there is no problem there, but what I want to do is to return all the matches in that same cell so I would need some sort of drop down list for that cell. That is the major question here. Am I able to do so and how?

I made one example file so you can check it out. It would be much easier to see what I'm looking from the example.
Please note that I used only one file for the example and that these 2 tables would be in the 2 different files in the real situation.
If you need any further information and details please, ask, I will provide them asap.

Any suggestion is highly appreciated, thanks.
Dean

example >>> exampleVLOOKUP.xlsx

EDIT: I use MS Office 2010.

[alansidman]

Look at the attached.

[oeldere]

See the attached file.

The result is on sheet new data.

I explained in my file how I made this.

Do you have questions or comments, just ask or comment.

[Dolke]

Thanks guys for quick replies and for taking interest.

I will take a look in both files and get back to you later on today.

Dean

[Dolke]

Hi guys.

I check both ways and although they are quite useful, they are not solution to the original problem.

Ill try to clear up what is my intention here:

Major keys in the table are the product codes. I'm purchasing some product for a period of lets say one year.
In that period I usually change suppliers. Reason for change is in most cases prize of the product, but can be some other reason as one (cheaper supplier) run out of specific product, so I'm bought from another one (which is more expensive), just to run continues business and make my customers satisfied, so I always keep my stock full and never run out of some products, even the my profit margin is less that ways...

Now back to the excel
So, key is product code. I would like, when I type for example "11" function looks up for the same product code in the second file or spreadsheet.
It returns supplier name and its corresponding prize. Result should be looking like this:

Please Login or Register  to view this content.

This is drop down list. If I chose AAA it gives me 100 prize. If I chose GDF it gives me 130 as a prize.

Notice that column with the names of the supplier and corresponding prizes are all in the same cell so they can be chosen from the drop down list.
All data is returned as a result of lookup in the 2nd file but I can chose from the results.

Thanks
Dean

[oeldere]

You get better advice if you add an excel example, without confidentional information.

Please also add the desired result.