Trying to Determine Cost based on multiple variables

[jeffamore]

Please refer to the image as it's the best way for me to explain what I'm trying to do. I want to look at the first three characters in the Indicator column (column A), look at the appropriate table that matches that value in columns F and G, grab the correct dollar amount in that table from column G based on the number of units in column B that corresponds to the number in column F, and then multiply B and F in column C. I've included the expected results in the image in columns J and K. This would be done over thousands of rows, so I only want to do this once and just be able to copy the formula all the way down column C. Any help is greatly appreciated!

[XLent]

if we assume your various rate tables are not "named" then, to keep things simple, I would do following:

1. insert 2 blank cells in F1:G1 such that F1:G19 become F2:G20 etc (but A:B remains as-is)

2. add a key to Col H

H2: =CHOOSE(1+COUNT(F2:G2),INT(H1)+0.9,F2,INT(H1)+F2/10^7)
copied down for all rows

3. apply following to derive Cost

C2: =B2*LOOKUP(LEFT(A2,3)+B2/10^7,H:H,G:G)
copied down

the above will replicate your expected results, and should be pretty quick to calculate.

NOTE: this does assume, per screenshot, that your rate tables are listed in numeric order from small to large - both in terms of "key" (101 to 103 etc) and in terms of individual bands therein (0 to n)

[6StringJazzer]

Welcome to the Forum jeffamore!

First, it is generally better to attach your actual file instead of a picture. Few people are going to take the trouble to create a new file based on your picture. That way we can implement a solution and give you the file back. See yellow banner at the top of the page.

Here is an alternative for a formula you can put in C, with no other changes, although I haven't tested it. If you have trouble with it attach your file.

Please Login or Register  to view this content.

Also I discourage the use of merged cells. Consider "Center Across Selection" to get the look you want.

[jeffamore]

I'm having a hard time replying and including the attachment with the workbook, but I appreciate the responses so far.

[XLent]

Hi, so, if you copy the formulas per Post #2 into your sample file (without modification) you should get your desired results.

the alternative approach, c/o 6SJ, will work but it will be volatile, and assumes that your rate tables will always fit within 5 rows and never overlap (which may, or may not be the case)