Quick question,, Multiple IF statements: want to add a "" if the result if FALSE/the reference cell I2 is blank

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
555
Office Version
2019
Platform
Windows
=IF(I2="Weld Rod","A",IF(I2="Flame Spray Poweders","B",IF(I2="Special Applicate Coatings","C",IF(I2="Liquid Pen Materials","D",IF(I2="Assorted Glues","E",IF(I2="Powders Blasting MTRL","F",IF(I2="Materials","G")))))))

If I2 is blank the result is: FALSE. How do I get it to display a blank cell if False?

I tried a ,"" after the first return result for each statement and tried a IFERROR for the entire formula but both are a no go.

Thank you

Carla
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,904
These kind of formulas are best written as lookup functions:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Flame Spray Poweders</td><td style=";">B</td><td style="text-align: right;;"></td><td style=";">Weld Rod</td><td style=";">A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Flame Spray Poweders</td><td style=";">B</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Special Applicate Coatings</td><td style=";">C</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Liquid Pen Materials</td><td style=";">D</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Assorted Glues</td><td style=";">E</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Powders Blasting MTRL</td><td style=";">F</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Materials</td><td style=";">G</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">I2,$L$2:$M$8,2,0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
555
Office Version
2019
Platform
Windows
I always think of the most complicated way first lol. This is much nicer, thank you very much!!
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
812
Office Version
2016
Platform
Windows
Try IF(I2="Materials","G","") instead of IF(I2="Materials","G")

What do you want to happen if I2 contains some other text? At the moment that would also return a FALSE or with the changed formula a null.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
555
Office Version
2019
Platform
Windows
To respond to Toadstool: I2 is a drop down list so they have to choose one of the provided selections. Thank you everybody :)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,203
Messages
5,485,332
Members
407,497
Latest member
astaha

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top