Forum Discussion

MrsRobinsonHeresToYou's avatar
MrsRobinsonHeresToYou
Copper Contributor
Mar 03, 2023

if then

'=if b15 is "Roof" then c15 "1", OR if b15 is "Door c15 "2", OR if b15 is "Window" then c15 "4", 

cant get it to work ... ?

thanks in advance

excel

1 Reply

Sort By
  • mathetes's avatar
    mathetes
    Silver Contributor
    Mar 04, 2023

    MrsRobinsonHeresToYou 

     

    There are at least two ways to do it. 

    Here, entered into cell C15, would be the easiest, but also the less robust, more problematic in the long run.

    =IFS(B15="Roof",1,B15="Door",2,B15="Window",4)

     

    Why is that more problematic in the long run, you ask: because you are "hard-coding the values" into the formula. But values like that often change over time, and hard-coded values are not "visible" ...

     

    So a more reliable and more readily maintained approach is to put those kinds of variables into a table and use one of the many LOOKUP functions in a formula such as this

    =VLOOKUP(B15,E13:F15,2,0)

    referring to a table like this in the range E13:F15

    I'm attaching a spreadsheet with both examples working.

Resources