Add many IFs to a formula

Question

Is there an easier and more compact way to write this formula?

[1]       [2]
A         0
B         5940
C         13860
D         22500
E         87300
F         378000

 =IF(Q27="A",0,IF(Q27="B",5940,IF(Q27="C",13860,IF(Q27="D",22500,IF(Q27="E",87300,IF(Q27="F",378000,"ERROR"))))))

So what it does is return the respective values in [2] if Q27 equals one of the letters in [1]. I managed to get the result I expected but I'd like to know if the code can be improved.

Thanks

Answer 1

The following formula:

=INDEX($B$2:$B$7,MATCH(E2,$A$2:$A$7))

Enter the value you're using to look up in cell E2. If you were to put the formula in cell Q27 as you specified, then you would get the return value in that cell. If you were to put D into cell E2, and have the formula in cell Q27, you would get 22500 in Q27.

Alternatively, if the helper cell E2 isn't an option, you could simply replace E2 in the formula with "A", or "B", or whichever specific value you're using to lookup. Be sure to include quotation marks around it. For example:

=INDEX($B$2:$B$7,MATCH("A",$A$2:$A$7))

Answer 2

That is what VLOOKUP is for. Put your table in A1:B6 then:

=VLOOKUP(Q27,A:B,2,FALSE)

Or you can "Hard Code" the VLOOKUP like this:

=VLOOKUP(Q27,{"A",0;"B",5940;"C",13860;"D",22500;"E",87300;"F",378000},2,FALSE)

Or:

=INDEX({0,5940,13860,22500,87300,378000},MATCH(Q27,{"A","B","C","D","E","F"},0))

Answer 3

If you have to hard-code the values then maybe,

=iferror(choose(code(upper(q27))-64, 0, 5940, 13860, 22500, 87300, 378000), "error")