Link data in different sheets into master view

[heikamp]

Hi all,

The situation is that I have a master sheet, which is my overview sheet with the columns model, price supplier 1, price supplier 2, etc
sheet 2 is pricing from supplier 1 (model, price)
sheet 3 is pricing from supplier 2 (model, price)

I want to merge data from sheet 2 and 3 into the master sheet
Supplier data model name can differ per supplier, so my master sheet model name is a combination of all different supplier model names for that product together
There must be a partial match where the whole of supplier model names matches partially with my model name in the master sheet
Also the order of products per supplier is different.

What would be the best method in matching the supplier model name with any row in sheet 1 and fill out the supplier price in the supplier column in the master sheet?

Thanks for all the help.
Gijsbert

[Keebellah]

Hi, welcome to the forum.
I have not looked at your file but if you have let's say your own product list in a worksheet and you have suppliers which have the same product but under another name I would do the following.
reserve one sheet for the master list in column A and leave the next 10 columns empty.
When you have a supplier with his own description for that same part you add the name next you yours in the next first empty column and so for all suppliers
So when you receive a list and search on the suppliers 'product name make a search in the area A:K and if found your name is the one in column A of the found row.
I do this when I allocate column headers to other files which have the same data but use different columns.
Hope my explanation makes a little bit sense

[heikamp]

Hi Keebellah,

I made progress, but I'm stuck right now.
Current situation, in the master I've got 3 columns with 3 product names for the same product.
If the supplier product matches with any of the 3 names, I want to execute the Vlookup
=VLOOKUP($B6,Supplier_1!$A$3:$B$1000,2,)/VLOOKUP($I$2,Rates!$Q$2:$R$50,2,)

Vlookup is currently only working on field B6
If I change $B6 to $B6:$D6 it isn't working

My intension is I want to use ANY of the fields (B6, C6 or D6) to function as Vlookup search key.
Is that possible?

Gijsbert

[Keebellah]

There is something I don't understand and that's because I'm not the great with formuls.
What does the / do ?

Please Login or Register  to view this content.

And is thsi formula on row 6 in the master under Supplier 1?

If your looking inside a range I think you should be using INDEX and MATCH and use an ARRAY formula Shift+Ctrl+Enter