I am racking my brain on the correct formula for this.

My source data is 20+ tables (geographical locations) used for metrics on build costs between locations. Each table (location) has 5 different sizing capacities for what we're building and the cost metric varies based on the sizing. This variable is located in one row across the table (x-axis). In the left most column, we have a list of cost premiums that vary by location e.g. labor premium, winterization, seismic conditions, etc. (y-axis). The sizing capacity affects the cost metric for each of these premiums. This table / matrix is repeated down the spreadsheet 20+ times with the change being the location, and thus the cost metrics for each premium x capacity.

I want to summarize this data in a semi automated way where if I select from a drop down: variable 1 - the location + variable 2 - the sizing capacity, it returns the correct cost premium. What is the best function for this and how would I go about it? INDEX, MATCH? VLOOKUP? XLOOKUP?

Assume I don't want to change the format of the source data. I don't mind adding data if I need to but wanted to see if there is a way to create a function without changing anything from the source.

ChatGPT.

I really am flabbergasted at how many posts for excel help there are when ChatGPT is a thing...

I know you said not wanting to change source formatting but is this something that could be in one table? Could some of the items become columns in the new combined data set? Usually a lot easier when set up like this. Would probably have to see a screenshot to better understand how it's set up right now though.

My initial thought is power query even though I know nothing about it other than that people use it to combine data from multiple tables/sheets/workbooks.

This sounds like a power query problem to turn everything into a single data source.

If the formatting is relatively consistent though, you could create a named range for each location's table, and pass that to one of the lookup/index functions, and then lookup based on the size. So something like

Index(named range passed from drop-down 1 for location,row #, match(size, array with sizes))

I think I get what you're saying. You have cost tables for locations. Rows are cost types and columns are different sizes. Values are the cost of that cost type at that size. Correct?

Without testing this, you could probably put your table names, cost types, and sizes into data validated cells to get drop downs. You could use dynamic named ranges for the cost types and sizes to dynamically reference the x and y axes values in your tables. From there, use MATCH() to find the row and column of the selected values and return them by using INDIRECT() to reference the address.


INDEX() and a double MATCH() works as well.

If all the tables are identical, it's an easy job of stitching them together with Power Query. Then use a Pivot Table to report the premiums. The will keep formatting consistent, your ranges will grow and shrink as needed, and you create comparisons and analyses.

If the tables aren't identical, can you update them to match up? Hardest part of anything like this is just cleaning the inputs.

DallasTeleAg said:

---

ChatGPT.

I really am flabbergasted at how many posts for excel help there are when ChatGPT is a thing...

---

This was probably a lot of help, glad you demonstrated your excel knowledge and provided OP the answer

DallasTeleAg said:

---

ChatGPT.

I really am flabbergasted at how many posts for excel help there are when ChatGPT is a thing...

---

Negative stars to you sir.

Give man a fish... blah, blah...