If/Or Formula - Which formula do I use?

[JenniferS30]

I'm trying to set up a formula that pulls a dollar amount from a separate
worksheet. If the cell in my worksheet contains the number 1, I want it to
pull a certain dollar amount. If the same cell contains the number 3, I want
it to pull a different dollar amount. If the same cell contains the number 6,
I want it to pull a different dollar amount. If the same cell contains the
number 11, I want it to pull a different dollar amounts. All of these dollar
amounts are listed in a worksheet in the same document. Can this be done? If
so, what type of formula should I use?

 

[Fred Smith]

An If statement like this should do what you want:

if(a1=1,certain dollar amount,if(a1=3,different dollar amount,if(a1=6,still
different,if(a1=11,more different,"Not 1,3,6 or 11"))))

Or you could use Vlookup.

 

[Gord Dibben]

How about a making a VLOOKUO table on sheet2 with the dollar amounts and a
VLOOKUP formula on sheet1 that pulls depending upon what value is in a cell?

Sheet2 Column A contains the numbers 1, 3, 6, 11 etc.

Column B contains appropriate dollar amounts.

Sheet1 has a variable number in A1

B1 contains this formula =VLOOKUP(A1, Sheet2!$A$4:$B$4,2,FALSE)


Gord Dibben MS Excel MVP