I have two sheets as shown below

This sheet provides a mapping between type and product

Type	Product
Xmas_Green	Xmas Tree
Xmas_Red	Xmas Tree
Xmas_Blue	Xmas Tree
Bunny_Red	Bunny
Bunny_Blue	Bunny

and the another sheet provides mapping between products and vendor as shown below

Product	Vendor
Xmas Tree	Xmas Corp Ltd
Bunny	Bunny Corp Ltd

Using the above sheets, I want to generate another sheet showing type to vendor mapping (based on product)

Type	Vendor
Xmas_Green	Xmas Corp Ltd
Xmas_Red	Xmas Corp Ltd
Xmas_Blue	Xmas Corp Ltd
Bunny_Red	Bunny Corp Ltd
Bunny_Blue	Bunny Corp Ltd

So far I have tried to use VSLOOKUP to match tables based on column but it expects 1:1 mapping to work. Another approach I'm trying is to count duplicates on Product and duplicate the Vendor based on Product count. This needs to scale up for 6000 records and VBA macros can be used. This is on 2016. I'm not an advanced Excel user and any help in the right direction is much appreciated.

CodePudding user response：

There are few ways to do that. XLOOKUP() may best fit.

=XLOOKUP(XLOOKUP(B10,$A$2:$A$6,$B$2:$B$6,""),$D$2:$D$6,$E$2:$E$6,"")

If you are on Excel-2016 then can use INDEX/MATCH like-

=INDEX($E$2:$E$6,MATCH(INDEX($B$2:$B$6,MATCH(B10,$A$2:$A$6,0)),$D$2:$D$6,0))

With VLOOKUP() can try-

=VLOOKUP(VLOOKUP(B10,$A$2:$B$6,2,FALSE),$D$2:$E$6,2,FALSE)

With most recent release of Microsoft-365 can use BYROW() for one go.

=BYROW(B10:B14,LAMBDA(x,XLOOKUP(XLOOKUP(x,A2:A6,B2:B6),D2:D6,E2:E6)))

And by FILTER() function.

=@FILTER($E$2:$E$6,$D$2:$D$6=FILTER($B$2:$B$6,$A$2:$A$6=B10))

CodePudding user response：

Another approach would be to use Power Query. Perhaps this is what you want if you are working with 6000 rows.

You could set up your two input tables as Excel Tables, let's call them tableType and tableProduct.

Click anywhere in the table and hold CTRL t. Then in the Table Design tab, enter the names as above.

You can then go into the Power Query editor and use the script below to merge the two tables.

Data > Get Data > From Other Sources > Blank Query

Then paste this script over the empty script that is written into the Advanced Editor.

let
   dimProduct = Excel.CurrentWorkbook(){[Name="tableProduct"]}[Content],
   dimType = Excel.CurrentWorkbook(){[Name="tableType"]}[Content],
   Source = Table.NestedJoin(dimProduct, {"Product"}, dimType, {"Product"}, "dimType", JoinKind.LeftOuter),
   #"Expanded tableType" = Table.ExpandTableColumn(Source, "dimType", {"Type"}, {"Type"}),
   #"Removed Columns" = Table.RemoveColumns(#"Expanded tableType",{"Product"}),
   #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Type", "Vendor"}),
   #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Type", type text}, {"Vendor", type text}})
in
   #"Changed Type"

Then Close and Load To and choose where you want your table to go. To update it, Right-Click and Refresh.