Question about if statement or formula?

JRyan

New Member
Joined
Mar 5, 2002
Messages
6
I have a set of six different dollar amounts. I want to write a formula (argument) so that if I pit a number (1-6) in a cell it will put the value (Dollar amount) I want assigned to that number in the cell next to it? Is that possible.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
On 2002-03-06 04:30, JRyan wrote:
I have a set of six different dollar amounts. I want to write a formula (argument) so that if I pit a number (1-6) in a cell it will put the value (Dollar amount) I want assigned to that number in the cell next to it? Is that possible.

How about:

=VLOOKUP(A1,{1,dol1;2,dol2;3,dol3;4,dol4;5,dol5;6,dol6},2,0)

where dol1 is the dollar amount associated with 1, dol2 the dollar amount associated with 2, etc.?

dol1, dol2,... are numbers and must be entered as constants between the braces.

If this is not what you want, try to be more specific.
This message was edited by Aladin Akyurek on 2002-03-06 06:52
 
Upvote 0
Looks like you could use VLOOKUP. Here's a quick example:-

Say in range A1:A6 you have the numbers 1 to 6 and in range B1:B6 you have your dollar amounts. In any cell (say C1) type a number between 1 and 6 and in another cell (say D1) type this:-

=VLOOKUP(C1,A1:B6,2,FALSE)

VLOOKUP can be confusing if you've never used it before. Basically it's saying take the value in C1, go and look in the first column in the range A1:B6 until you find a value which matches that in C1, go to column 2 (i.e. B1:B6) and return that value (the dollar amount). The FALSE part is required if the list isn't sorted. If it is, then you can omit it.

HTH,
D
 
Upvote 0
I'll try to be more specific. In cell C5 I want to be able to enter a number.(1,2,3,4,5, or 6). I want the dollar amount for that number to appear in C7.
The value for 1 is $32.50
The value for 2 is $22.50
The value for 3 is $37.50
The value for 4 is $25.00
The value for 5 is $45.00
The value for 6 is $32.50

Thanks
JRyan
 
Upvote 0
On 2002-03-06 04:44, dk wrote:
Looks like you could use VLOOKUP. Here's a quick example:-

Say in range A1:A6 you have the numbers 1 to 6 and in range B1:B6 you have your dollar amounts. In any cell (say C1) type a number between 1 and 6 and in another cell (say D1) type this:-

=VLOOKUP(C1,A1:B6,2,FALSE)

VLOOKUP can be confusing if you've never used it before. Basically it's saying take the value in C1, go and look in the first column in the range A1:B6 until you find a value which matches that in C1, go to column 2 (i.e. B1:B6) and return that value (the dollar amount). The FALSE part is required if the list isn't sorted. If it is, then you can omit it.

HTH,
D
 
Upvote 0
I understand this one and it does exactly what I want it to. If I could just push it one step further. If I don't put a number (1 - 6)in cell C1, can I get D1 to show nothing. ( Now it shows #N/A if I don not enter in C1 )

Thanks so much to the both of you for your fast response and help. LIFE IS GOOD AGAIN!!
JRyan

On 2002-03-06 04:44, dk wrote:
Looks like you could use VLOOKUP. Here's a quick example:-

Say in range A1:A6 you have the numbers 1 to 6 and in range B1:B6 you have your dollar amounts. In any cell (say C1) type a number between 1 and 6 and in another cell (say D1) type this:-

=VLOOKUP(C1,A1:B6,2,FALSE)

VLOOKUP can be confusing if you've never used it before. Basically it's saying take the value in C1, go and look in the first column in the range A1:B6 until you find a value which matches that in C1, go to column 2 (i.e. B1:B6) and return that value (the dollar amount). The FALSE part is required if the list isn't sorted. If it is, then you can omit it.

HTH,
D
 
Upvote 0
Try this:-

=IF(ISNA(VLOOKUP(C1,A1:B6,2,FALSE)),"",VLOOKUP(C1,A1:B6,2,FALSE))

HTH,
D
 
Upvote 0
BINGO!!!!!
I can't thank you guys enough!
I've talked to so many people who I thought were wizards in the program and they had no idea even if it could be done....
YOU GUY'S ARE GREAT!!!
I won't loose the address for this board.
JRyan

On 2002-03-06 06:18, dk wrote:
Try this:-

=IF(ISNA(VLOOKUP(C1,A1:B6,2,FALSE)),"",VLOOKUP(C1,A1:B6,2,FALSE))

HTH,
D
 
Upvote 0
Or try this

=OFFSET(A1,B2,0)

When A2:A7 contain the dollar amounts and B2 is the cell you enter the number into.
 
Upvote 0
On 2002-03-06 05:23, JRyan wrote:
I'll try to be more specific. In cell C5 I want to be able to enter a number.(1,2,3,4,5, or 6). I want the dollar amount for that number to appear in C7.
The value for 1 is $32.50
The value for 2 is $22.50
The value for 3 is $37.50
The value for 4 is $25.00
The value for 5 is $45.00
The value for 6 is $32.50

Thanks
JRyan

=VLOOKUP(A1,{1,32.5;2,22.5;3,37.5;4,25;5,45;6,32.5},2,0)

If you want some control over what can be inputted in A1, use:

=IF(AND(ISNUMBER(A1),A1>=1,A1<=6,INT(A1 =A1)),VLOOKUP(A1,{1,32.5;2,22.5;3,37.5;4,25;5,45;6,32.5},2,0),0)

Format the cell of this formula as number with 3 decimals, if so desired.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top