IF formula doesn't work when I use letters in the reference cell instead of numbers

[AFawcus123]

=IF(B17=HBLLAV,"HAND & BODY LOTION Lavender",IF(B17=HBLSND,"HAND & BODY LOTION Sandalwood & Lavender"

I can change the contents of the reference cell B17 to a number like 1.1 and put that number into cell B17 and it works, it just won't work when I use letters. I've tried cell format as text and general. What am I missing

 

[myall_blues]

Because it's text you have to add quotes.

=IF(B17="HBLLAV","HAND & BODY LOTION Lavender",IF(B17="HBLSND","HAND & BODY LOTION Sandalwood & Lavender","Undefined"))

 

[AFawcus123]

oh my! that simple eh?! wow, thank you!!!

 

[Alex Blakenburg]

I know @myall_blues has provided the solution to your issue but as a suggestion, I would generally use a mapping table and Vlookup or Xlookup to do that sort of thing but if you don't want to use a mapping table consider using the new Switch function.

=SWITCH(B17,"HBLLAV","HAND & BODY LOTION Lavender","HBLSND","HAND & BODY LOTION Sandalwood & Lavender","Undefined")

Excel Switch function – the compact form of nested IF statement

This article introduces you to the Excel SWITCH function, describes its syntax and provides a couple of use cases to illustrate how you can simplify writing nested IFs in Excel.

www.ablebits.com

 

[myall_blues]

@Alex Blakenburg yes my general rule is if you need more than three nested IFs it's time to find another way.