Multiple formulas

[Melody2]

I want to have one formula in a cell that does multiple functions. I need it to look at a column with a code, vlookup on a secondary sheet for the payment rate for that code. If the payment rate figure is below $1000.00 multiply by 3. If the figure is above $1000.00 multiply by 2, and finally if the figure is above $2000.00 multiply by 1.5. Can this be done with one formula that can be copied down an entire column? I hope I am explaining this properly. I would appreciate any help you could give.

 

[Airfix9]

=IF(VLOOKUP(A2,Sheet2!A:B,2,FALSE)>=2000,B2*1.5,IF(VLOOKUP(A2,Sheet2!A:B,2,FALSE)>=1000,B2*2,B2*3))

Start with the biggest lookup ($2000) first, then work backwards.

 

[Melody2]

I understand the basic setup of this formula, which look as if it will work for me. But I think I am brain dead from the holidays. What is the B2 cell referencing? I tried entering this in and referencing my secondary sheet, but the result was zero. sorry to bother you again.


=IF(VLOOKUP(A2,Sheet2!A:B,2,FALSE)>=2000,B2*1.5,IF(VLOOKUP(A2,Sheet2!A:B,2,FALSE)>=1000,B2*2,B2*3))

 

[Airfix9]

Sorry, A2 (in this example) is the cell showing the code. B2 is the cell showing the value to be multiplied by 1.5, 2 or 3.

Sheet2 has the codes in column A and the payment rates in column B.

Hope that helps.

 

[Scott Huish]

Another possibility:

=LOOKUP(LOOKUP(A2,Sheet2!$A:$B),{0,3;1000,2;2000,1.5})*B2