look up, match index? multi variable formulas

J

Jason

I have a possibility of entering a dollar value in C10 - C14. Once a number
is used in C10 it will not be used again in the next months D10, E10 and so
on. The same for C11-14. there might not be a number entered in those sells
for months to come. IT might be G11 or later on. I set up another cell with
the following formula:

=IF(C10=0,0,(LOOKUP(C10,Sheet1!$C$7:$C$12,Sheet1!$E$7:$E$12)))

This references table that looks like this:
Period 1 Periood 2 Period 3 Period 4
Level 1A 1 to 50 $250 0.01% 12.50% 12.50% 20.00%
Level 2A 51 to 150 $12,500 0.01% 8.33% 8.33% 8.33%
Level 3A 151 to 500 $37,500 0.01% 6.25% 6.25% 6.25%
Level 4A 501 to 750 $125,000 0.01% 5.00% 5.00% 5.00%
Level 5A 750 to 1000 $187,500 0.01% 4.17% 4.17% 4.17%
Level 6A 1001+ $250,000 0.01% 3.57% 3.57% 3.57%

In this case the result of the formula is: 12.5%

This is great, however, I need to formula to take into consideration in the
next cell that instead of period 1, I need the result to be period 2 and so
on.

I hope this is clear what I am asking, if not let me know. Any help would
be greatly appreciated.

Thanks!
 
P

Paul C

Using Vlookup will allow you to specify a column
=IF(C10=0,0,VLOOKUP(C10,Sheet1!$C$7:$G$12,3,true))
would look up results in Column E (the third column of your array)

You can replace the 3 with some formula that evaluates to your desired
period (Column 1 is you lookup value so Period 1 would be column 2, Period 2
- Column3, etc..)

Since you did not give an example of the are using these formulas, I cannot
specifiy exactly how to vary the Vlookup column to give you your desired
period

Hope this get you started in the right direction
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top