# Formula Query

#### AndyinEngland

##### New Member
I need to be able to put a value in a cell which will then display another value. eg. I want to be able to enter "1" (finishing position in a race) and have the value "22" displayed (pts. awarded for finishing 1st)
Can anybody help? or at least give me some kind of solution!
Thanks.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Please clarify: do you mean you want a "22" to appear in the same cell that you typed a "1" into?

set up a table with position in column 1 and points in column 2.(say range = A1:B10)

say you put your "place" in cell D1 - in your results area, in the cell next to your position (say E1), place the following formula...

=VLOOKUP(D1,A1:B10,2,False)

This will return the points value of a 1st place finish in the cell next to the place. Note that in your original table, the PLACES column (say P1 to P10) needs to be in ascending numeriacl order for VLOOKUP to work.

HTH

On 2002-09-02 07:08, SteveInAlabama wrote:
Please clarify: do you mean you want a "22" to appear in the same cell that you typed a "1" into?
Not necessarily . . . as long as I can add up all the points in a season when needed, the "22" could appear in another column.

Andy,

So have you tried my example, or do you need more detailed explanation.....?

will
will try your solution tonight, sounds like its what i want
Thanks alot

simple formula would do..

eg if(A1=1,22,0)...

you could build this up so if you were second...eg16 points...would be
=IF(A1=1,22,IF(A1=2,16,0))and so forth

regards
chef

Chef.... maybe suck eggs info etc...but...you know you can only have upto 7 nested IF functions though, which is why i recommended the VLOOKUP option...formula less complex AND if points for positions change, only one table needs to be updated instead of all those formulas...

Will,

Not taking away the usefulness of vlookup....was merely suggesting an alternative method..and sometimes a simple formula can work for simple tasks.
As for 7 if statements..you can actually get around this by using named formula..ie name your first 6 ifs say..test1...another 6 say test2....then if(test1,[action](iftest2...and so forth.
Don't really recommend using more than 7 if statements though and in that circumstance,yes vlookup or VB would be a more productive solution

regards
chef

Replies
0
Views
446
Replies
15
Views
652
Replies
1
Views
512
Replies
2
Views
286
Replies
0
Views
288

1,202,905
Messages
6,052,481
Members
444,586
Latest member
Godtymer

### 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.

### Which adblocker are you using?

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

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