Summary Tab from Inconsistent Data Structure and Location

[jdutle]

Hello!

I am trying to create a summary tab in excel that will look up data in other tabs based on specified criteria, but the structure of those other tabs and location of data is not always consistent. I’ve used a combination of Vlookup, Index, and Match to achieve the desired result, but I was wondering if there was a more streamlined way to do it, or if anyone else had any other ideas. I know it would make the most sense to structure each tab in the same way, but that is not an option in this circumstance.



My current solution finds the location of the Manufacturer name row, matches the brand, and then pulls the price on to the summary tab. I feel like there is a simpler way to identify the row with Manufacturer name. Its currently “MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)&":"&MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)” Its basically the same formula twice to return a row reference of #:#.



I have attached a sample file for your reference. The formula in question is in yellow. Thank you in advance for any ideas or suggestions.

 

[bosco_yip]

Try this formula solution without column A "sheet name helper"

In C8, formula copied across right to G8 :

=OFFSET(INDIRECT("'"&SUBSTITUTE($B7," Brands","")&"'!B1"),MATCH("Price with Tax",INDIRECT("'"&SUBSTITUTE($B7," Brands","")&"'!A:A"),0)-1,(INT((COLUMN(A1)-1)/2)+1)*3)

Then, select C8:G8 >> formula copied to C11:G11