Change value of a cell based on another value

Im trying to use an IF statement in Excel that gives a result based on the value of another cell. So, for instance, if cell A10 has a value of 10780, I want the cell D10 to have a value of 90310011. I can do this with the code: =IF(A10=10780;90310011). This works

However, I want to be able to add more values with corresponding new numbers in the same cell. So if the first cell was 10782, I want the value to be 90310012

As you can see in the picture, I tried to do this with AND and also with OR. I get the result 0 with both so its not working.

How can I do this?

3 Answers

The IF formula has 3 parameters, where you only used 2.

=IF( condition ; true ; false )

The formula at the condition side is evaluated, and its either true or false.

When its true, the formula that is located in the true section will be executed.

When its false, the formula in the false section will be executed.

A formula can just be a reference to another cell or even a static value. If this is the case, this is returned.

So: =IF ( 1=2 ; "it is true" ; B4 ) will evaluate if 1=2. Its not true, so whatever value is stored in cell B4 is now returned.

Because you can also enter formulas in the true or false result, you can nest IF statements. For example:

=IF( 1=2 ; "first is true" ; IF( 1=3 ; "second is true" ; "Neither are true") )

This will result in "neither are true".

Of course, the actual condition can refer to other cells like in your question too.

Nest your subsequent If statement within the false option of the first If...

=IF(A10=10780,90310011,(IF(A10=12072,90310012,(IF(A10=[etc])))))

Can get messy though.

Could you update it with a macro? or does it have to be done as a formula?

It seems like your values are related by an equation... you may want to figure that out (if that's the case)

if not, then what you want are nested IF-statements. Other people seem to have pointed that out, so I'll write it down for you in a more didactic structure

=IF ( A10=10780 ,90310011 ,IF ( A10=12072 ,90310012 ,IF ( A10=[...] ) ) )

See how they're all nested whithin one another? that makes the verification of your (inner) condition dependant on the value of the (outer) condition