Need experts help on this after racking brain over and over. I have a data sheet, 'DATA', that contains Product and Pricing detail by row. Each row will have a product name, Product A, Product B, Product C....etc, and location name, Location 1, Location 2, Location 3....etc. On another worksheet I have a table that's a pricing schedule, 'PRICING SCHEDULE', for each product at each location. I've created a Named Range for each Product/Location line on the Pricing Schedule based on the previous product and location name example I gave looks like this: PRODA1, PRODB2, PRODC3.

A product can be sold at multiple locations with a different price for each. I'm needing to find the minimum price on the associated Pricing Schedule for each line item on DATA sheet, based on the last Transacted Price listed in each row item on DATA sheet. Here's my issue:

I'm using Aggregate formula that works perfectly if I just directly type in the named range, but unfortunately, I have a dynamic file so I'm referencing the product and location to find the correct value in the other sheet. This has me creating a concatenation where I combine LEFT function to get the 1st 4 characters of Product Name field and RIGHT function for last character on both the Product Name field and the Location Name field. So for a row where product is, 'Product B' for location 'Location 4', I get a concatenation of, ProdB4 which I have a named range setup as in the Pricing Schedule. Unfortuntatly when I reference the cell that contains the concatenation, it generates a VOLATILE formula. Here is my forumula:

AGGREGATE(15,6,INDIRECT(CONCATENATE(LEFT(B2,4)&RIGHT(B2,1)&RIGHT(H2,1))/(CONCATENATE(LEFT(B2,4)&RIGHT(B2,1)&RIGHT(H2,1)))>$P2),1)


If I just type in PRODB4 where I am referencing my named range, the formula works perfect. I know there might be another formula option, but I don't want to get into a long IF statement, so would really need to keep using AGGREGATE. Open to renaming Named Ranges if you have better sugestion, as well as obvisouly any formula changes you recommend.

Thank you for your help on this.