Change the value of a cell based on another ... ?

[robbaldock]

Hi all,

Firstly, as always, apologies if this has been answered several times already! I have searched and tried various answers to no avail so now seeking professional help!?!?!

Basically, all I wan't to do is change the value of one cell based on what is entered into another. To be exact; I want CELL K6 to display a result determined by CELL D6 .. so ..

IF D6=12 then I wan't K6 to show 34.00, IF D6=24 then K6=33.00, IF D6=36 then K6=32.75, IF D6=60 then K6=32.50 .. now it all sounds simple when you put it like that, but I have tried using IF statements, AND, OR and even conditional formatting and I just can't get it to work!

What am I doing wrong?!?!

Any help you can give would be much appreciated.

Thanks,
Rob.

 

[sheetspread]

Excel 2010
	D	E	F	G	H	I	J	K
6	60							32.5
Sheet6

Cell Formulas
Range	Formula
K6	=VLOOKUP(D6,{12,34;24,33;36,32.75;60,32.5},2,0)

 

[MarcelBeug]

Best way to do it is to set up a list, e.g. in sheet2
A B
Base Result
12 34.00
24 33.00
36 32.75
60 32.50

Then in K6: =VLOOKUP(D6,Sheet2!$A$2:$B$5,2,0)

This will look for D6 in Sheet2!A2:A5 and return the corresponding value from column B. The 0 is for exact match.
Alternatively you can use 1 if D6 can be any value in between e.g. 15 would return 34.00.

Adjust range Sheet2!$A$2:$B$5 as appropriate.

If this list is subject to frequent changes, I would recommend converting it to a table and reference the table in the VLOOKUP: =VLOOKUP(D6,tablename,2,0)