Horizontal Name

[Barrybruce2]

There are two worksheets related to inventory in the Test.xlsx file, the master sheet has 90% of the data needed while the supplemental sheet has the category data needed to complete the report.

All of the items in inventory are numeric with some alpha characters toward the end of the item name. This is the key feature in both worksheets. The supplemental worksheet has the name of the category at the top of each column with all corresponding item names below it.

We need to extract the Category name from the supplemental data sheet and add it to the master sheet. The name of each category is only found on Row #1 of the supplemental sheet. We have tried using Hlookup but I always get #value as a response.

 

[AliGW]

Try this:

=INDEX(Supplemental!$B$1:$D$1,SUMPRODUCT((Supplemental!$B$2:$D$15=C2)*(COLUMN($B$2:$D$15)-1)))

 

[AlanSidman]

1. Use Power Query to unpivot the Supplemental Data
2. Use the unpivoted data in a Index/Match formula