Lookup Question

[NextYearSox]

I am building an analysis with for three confidence levels, and I want to vary some of the cells based on the confidence levels. I'm using the following formula:

=LOOKUP(C1,{"High","Medium","Low"},INT(SUM(G22:G23)*0.1))

which works for the "High" case. What "punctuation" do I need to use to add the forumla's for the other two cases?

 

[Dryver14]

I'm afraid I can not sort out the puntuation for you but what else could appear in cell C1 as whether it said High Medium or Low it would still sum
G22 and 23

 

[T. Valko]

I am building an analysis with for three confidence levels, and I want to vary some of the cells based on the confidence levels. I'm using the following formula:

=LOOKUP(C1,{"High","Medium","Low"},INT(SUM(G22:G23)*0.1))

which works for the "High" case. What "punctuation" do I need to use to add the forumla's for the other two cases?

With only 3 conditions you can just use nested IFs.

=IF(C1="high",INT((G22+G23)*0.1),IF(C1="medium",do this,IF(C1="low",do this,"")))

 

[NextYearSox]

It is only three cases now, but in the future I may add a fourth - and maybe a fifth. So nested If statements are painful. I'm hoping to avoid that.

I know the Lookup case works in certain conditions:

=LOOKUP($C1,{"High","Low","Medium"},{1,2,3})
=LOOKUP($C1,{"High","Low","Medium"},A1:A3)

I just can't figure out how to make it work with non-consecutive cell references or three discrete formulas. It seems that it should work.

 

[barry houdini]

Try using MATCH and CHOOSE, e.g.

=CHOOSE(MATCH($C1,{"High","Low","Medium"},0),formula1,formula2,formula3)

formula1, formula2 etc, can be just cell references or numbers or some other calculation

 

[T. Valko]

It is only three cases now, but in the future I may add a fourth - and maybe a fifth. So nested If statements are painful. I'm hoping to avoid that.

I know the Lookup case works in certain conditions:

=LOOKUP($C1,{"High","Low","Medium"},{1,2,3})
=LOOKUP($C1,{"High","Low","Medium"},A1:A3)

I just can't figure out how to make it work with non-consecutive cell references or three discrete formulas. It seems that it should work.

If you're going to be adding conditions in the future then use a lookup table.

High...formula for "high"
Low...formula for "low"
Medium...formula for "medium"

Let's assume that 2 column table is in the range A1:B3.

Then:

=IF(C1="","",VLOOKUP(C1,A1:B3,2,0))

 

[NextYearSox]

Thanks for the suggestions. The =CHOOSE(MATCH... formula was the right mix of function and simplicity that I was looking for.