MrExcel Publishing
Your One Stop for Excel Tips & Solutions

values


Posted by Brian on November 10, 2001 10:18 AM

Aloha,
I would like to type a value in say A1 and a corresponding number appears in A2. As an example, entering 1 in A1 and the value 1000 appears in A2. I would like to have at least 20 enteries with corresponding values appearing. I used =if(A1=1,1000) but I can't expand it.

Mahalo....Brian


Posted by Aladin Akyurek on November 10, 2001 10:26 AM

Do you mean

A1 = 1
A2 = A1 *1000
A3 = 2
A4 = A3 * 1000

or what?

==========

Posted by Brian on November 10, 2001 11:07 AM

Aloha,

Thanks for the fast response. What I mean is I have different models of cars with different weights. Car 1 weighs 2000 lbs, car 2 weighs 2500 lbs etc. When I enter 1 in cell A1, the 2000lbs appears in A2 automatically. If I enter 2 in A1, 2500 lbs appears in A2 automatically and so on for cars 3, 4, 5 etc with each car having a corrsponding weight appearing in A2

Posted by Mark W. on November 10, 2001 11:29 AM

Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0) (nt)

Posted by Brian on November 10, 2001 1:44 PM

Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0) (nt)

Aloha Mark,

It doesn't work.....

Thanks for trying

Brian

Posted by Mark W. on November 10, 2001 1:59 PM

Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)

It most certainly does... care to try again?

Posted by Aladin Akyurek on November 10, 2001 2:06 PM

Vlookup

Brian,

What Mark is suggesting is that you have a table in which you look up. From your description he concluded that you have combinations

1 2000
2 2500
3 3000
4 3500
5 4000

If the combinations are different (not this regular), modify the array table

{1,2000;2,2500;3,3000;4,3500;5,4000}

int one that fits your situation.

Aladin

===========


Posted by Brian on November 10, 2001 2:09 PM

Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)

Mark,

I'm using 97, does that make a difference? I've copied and pasted and get an error message.

Brian

Posted by Mark W. on November 10, 2001 2:36 PM

Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)

I'm using Excel 97 too. What error message are
you getting? Did you inadvertantly copy the "(nt)"
off the subject line?

Posted by Brian on November 10, 2001 3:43 PM

Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)

Mark,

The error message I get is:

The formula you typed contains an error.

The formula I used was =if(a1=1,1000)+if(a1=2,2000) etc. but I can only nest 7 of these. Your way I can use more than 7 numbers

Brian

Posted by Brian on November 10, 2001 3:45 PM

Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)

Mark,

I got it!......Much MAHALO!!!!!

Brian

Posted by Brian on November 10, 2001 3:48 PM

Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)

Mark,

What does the 2 and 0 stand for at the end of the formula?

Brian

Posted by Mark W. on November 10, 2001 4:01 PM

Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)

As Aladin pointed out...

{1,2000;2,2500;3,3000;4,3500;5,4000}

is a 2-column lookup array. When I specify 2 as
the 3rd argument in the VLOOKUP function I'm
asking it to return a value from 2nd column of the
lookup array. When I set VLOOKUP's optional
4th argument to 0 or FALSE I'm insisting that
the value in A1 match a value in the 1st column
of the lookup array EXACTLY! I recommend that
you take a look at the Help Index topic for
"VLOOKUP worksheet function" for a more complete
discussion of this function.

Posted by Brian on November 10, 2001 4:07 PM

Re: Use =VLOOKUP(A1,{1,2000;2,2500;3,3000;4,3500;5,4000},2,0)

Mark,

Thanks, I understand!

Here's another if you have the time:

I would like to assign a value to a range of numbers

1-99= -10
100-199= +10....etc

Brian

Posted by Aladin Akyurek on November 10, 2001 4:14 PM

Maybe: =IF(A1<100,-10,INT(A1/100)*10) (NT)

Posted by Mark W. on November 10, 2001 4:19 PM

I'm not quite sure...

...what you mean by "assign a value to a range...",
but I believe you mean if A1 is between 1 and 99
inclusive return "-10", and if A1 is between
100 and 199 inclusive return "+10", etc...

The formula for this would be...

=VLOOKUP(A1,{1,"-10";100,"+10";200,#N/A},2)

If I've misconstrued your intent please provide
a sample data set along with the expected
solution.