I renamed some of your table ranges in order to use them in formulas with the INDIRECT function, for example, Customer_1 for the cities under that customer.
For B column, this formula:
I use SUBSTITUTE because space cannot be part of a named range title, so I replace space with underline(_) to make the text in A3 (Customer 1) read (Customer_1) instead, thus matching respective named range.
For the trades ranges no substitution was required, so I use this formula in column D:
For Opp life and Budget, I combine INDIRECT & RIGHT functions, like in this formula in column G:
The RIGHT function picks the key characters from the column headings, such as "life" and "budget" to build desired named range with the INDIRECT function.
Please check file and let us know how it goes.
Good luck!
Bookmarks