Complicated vlookup with range values

[jcaps]

I have a table with ranges in A:A, and their corresponding values in B:B.

The challenge is that the values in B:B have ranges of their own.

Please see attached: Screen Shot 2012-11-05 at 2.07.01 PM.png

I want to have a cell (let's say D1) with an input value that spits out an output value (let's say E1) corresponding to the attached table.

For example: If my D1 value was $50, then my E1 value should be $39.50 (that's the easy part).
The hard part: If my D1 value is $1,250 my E1 value should be ~$216.15.

Any ideas?

Thanks for the help.

[ConneXionLost]

I can't see how you got $216.15 dollars from $1,250. Can you explain how this is calculated?

[protonLeah]

Do not post pictures to the forum. Post workbooks only; so that no one has to recreate your work.

[jcaps]

RE: protonLeah - how do I attach a workbook?

RE: ConneXionLost - in B8 it says: "$118.65 plus $48.75/$1000 or part thereof over $1000"
So: $118.65 (base rate) + $48.75 (1st additional $1000) + $48.75 (2nd additional $1000) = $216.15

Is that more clear?

Cheers.

[ConneXionLost]

How to attach a sample Excel workbook:

1. Below the "Quick Reply" thread frame, click the "Go Advanced" button.
2. On the edit toolbar, click the "Attachments" button.
3. In the "File Upload Manager" window, click the "Add Files" button.
4. In the "Upload Files from your Computer" window, click the "Select Files" button.
5. In the Windows Explorer window, locate and select your workbook, then click the "Open" button.
6. In the "Upload Files from your Computer" window, click the "Upload Files" button.
7. In the "File Upload Manager" window, click the "Done" button (lower right of screen).

[ConneXionLost]

So if the input value is $1000 the output is $118.65, but if the input value is $1001, the output is $216.15?

[jcaps]

Here's the workbook... I hope...

question.xlsx

[jcaps]

Close
If the input value is $1000, then the output value is $118.65.
If the input value is $1001, then the output value is: $118.65 + $48.75 = $167.40
If the input value in $1101, then the output value is: $118.65 + $48.75 + $48.75 = $216.15.

And it looks like I made an error:
If the input value is $1201, then the output value is: $118.65 + $48.75 + $48.75 + $48.75 = $264.90

[ConneXionLost]

Ah, so it is $48.75 for every $100 over $1000, not for every $1000 over $1000?

But if true, then $10,000 would carry a charge of $4,506.15?

[jcaps]

Ah man, I was totally confused. It is for every $1000, not for every $100. Totally my bad

So let's try this again:
If the input value is $1000, then the output value is $118.65.
It the input value in $1001, then the output value is $118.65 + $48.75 = $167.40.
If the input value is $2001, then the output value is $118.65 + $48.75 + $48.75 = $216.15

I guess it doesn't help when I confuse things even more. Sorry.

[Haseeb Avarakkan]

Set up a 3 column LOOKUP table, with your amount range & it's corresponding values, then Input value in E2,

=LOOKUP(E2,A2:B13)+INT((E2-1)/1000)*LOOKUP(E2,A2:C13)

See the attached.

Hope this helps.

[jcaps]

Set up a 3 column LOOKUP table, with your amount range & it's corresponding values, then Input value in E2,

=LOOKUP(E2,A2:B13)+INT((E2-1)/1000)*LOOKUP(E2,A2:C13)

See the attached.

Hope this helps.

This seems to work well for the values from 0 to 10,000, but the values from 10,001 upwards don't add up for me. So close, but I think we're not quite there.

I think it has to do with the part of the equation "INT((E2-1)/1000)", where when we get into 10s of thousands and hundreds of thousands the 1000 divider no longer works.

[Haseeb Avarakkan]

Try this:

=LOOKUP(E2,A2:B13)+INT(E2/LOOKUP(E2,A2:A13))*LOOKUP(E2,A2:C13)