Enter set value depending on data in another cell

[Excelarry]

I wish to automatically fill in column 'B' depending what someone enters in column 'A'

For example, if someone enters the value '43' in column 'A', then I want column 'B' to automatically enter '100,000'
Column 'A' is using Data validation, therefore has a limited field of entry (see attached list)

I've spent the last 2 hours trawling through answers, however, most of the answers go very much in depth with very complicated formulas and calculations, and nothing seems to 'ask' something this simple.

Thank you in advance.

<colgroup><col style="width: 120px"><col width="120"></colgroup><tbody>
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]

[TD="align: center"]35[/TD]
[TD="align: center"]100000[/TD]

[TD="align: center"]36[/TD]
[TD="align: center"]150000[/TD]

[TD="align: center"]37[/TD]
[TD="align: center"]250000[/TD]

[TD="align: center"]38[/TD]
[TD="align: center"]400000[/TD]

[TD="align: center"]39[/TD]
[TD="align: center"]650000[/TD]

[TD="align: center"]40[/TD]
[TD="align: center"]1000000[/TD]

[TD="align: center"]41[/TD]
[TD="align: center"]1500000[/TD]

[TD="align: center"]42[/TD]
[TD="align: center"]2500000[/TD]

[TD="align: center"]43[/TD]
[TD="align: center"]4000000[/TD]

[TD="align: center"]44[/TD]
[TD="align: center"]6500000[/TD]

[TD="align: center"]45[/TD]
[TD="align: center"]10000000[/TD]

</tbody>

 

[vlady]

maybe create a table containing those two digit numbers and with their counter parts "100000" then do a vlookup

=VLOOKUP(A1,D4:E14,2,0)

D4:E14 -> is the table
or you can tell us where did those "100000" came from a formula or computation maybe?

 

[Excelarry]

Hi vlady
Thank you for your quick response.
That was a huge help, thank you.
I'll explain a little further.

I modified your answer, which fixed the first cell. I used
=VLOOKUP(B7, Data!C4:D14,2,0)

I have my data on a 2nd 'tab' (called 'Data') Columns C & D (4 -to- 14)
The users fill in my first tab in column B, (by using validation data, i.e. a little pull down menu) and then I want the cell next to it (in column C) to automatically put in the relevant value.
The above string works, however, I now have 2 questions...

1) I wish to 'pull down' that formula down 100+ rows, but how can I make it use the same 'table' please?
The pull down changes the next row to:
=VLOOKUP(B8, Data!C5:D15,2,0)
When it should be:
=VLOOKUP(B8, Data!C4:D14,2,0)
Manually changing each one will take me a month of sundays. Can a $ sign be used somewhere for that?

2) What does the '2,0' do at the end of your formula?

Thank you in advance

 

[vlady]

We have to make the range absolute

=VLOOKUP(B7, Data!$C$4:$D$14,2,0) -> note the dollar signs.

$B7 -> b will not change when drag to the right but changes 7 when drag down
B$7 -> 7 will not change when drag down but changes B when drag to the right
$B$7 -> the reference will not change.

The 2 represents what column should return since we have two columns C and D and want to return the values in D so we use 2
the 0 (zero) tells vlookup to find the exact match

 

[Excelarry]

vlady, you are an absolute star ;o)
You've not only explained my problem very easily and understandable, but solved it all, very quickly indeed.

You did it so quickly, I didn't even have time to post the link to make it easier to explain. (not that you needed it, as it seems)
Anyway, it works, as I've done it on this Google doc (its just a copy of my original) if you wanted to see it.
http://goo.gl/59SC8Y

Thank you once again.

 

[vlady]

Nice to hear that. Your workbook is nice to except the merge cells in I and j column.
in A2 you could also replace that formula with
from
=SUMIF(A7:A101,"Tiberium",C7:C101) to

=SUMIF($A$7:$A$101,A$2, $C$7:$C$101) -> note the A don't have the dollar sign drag to the right. then you can change those also as drop down list (A2 to G2)

=COUNTIF(A7:A100,"Tiberium") to
=COUNTIF($A$7:$A$100,A$2) -> drag to the right also


Glad to help.
Regards,
vladimir