Multiple formulas

Melody2

New Member
Joined
Dec 29, 2005
Messages
4
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
=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

New Member
Joined
Dec 29, 2005
Messages
4
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

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Another possibility:

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

Watch MrExcel Video

Forum statistics

Threads
1,118,386
Messages
5,571,829
Members
412,421
Latest member
grace_abar
Top