Excel IF "text" then number, IF another "text" then another number

excel if-statement formula statements
user3491609 picture user3491609 · Apr 3, 2014 · Viewed 9.3k times · Source

In column B I want a formula that says:

  • If column A is Medium then use the value in B2 which is 150,000; if A is High then use the value in B1 which is 300,000; if A is Blank then use value in B3 which 50,000

I have bits of the formula but don't know how to include all in one formula.

=IF(ISBLANK(A9),"50,000")

Please help!

Answer

nutsch picture nutsch · Apr 3, 2014

You can do a if else structure, as in

=if(A9="Medium",$b$2,if(a9="High",$b$1,if(isblank(a9),B3,"")))

or a simpler index match:

=index($B$1:$B$3,match(A9,{"High","Medium",""},0))

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More
PivotTable to show values, not sum of values

I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g. Name Jan Feb Mar Apr Bob 12 10 4 3 5 James 2 6 8 1 15 etc. My starting point …

Read More