IF Function with multiple return values

J

jerminski73

I currently have the following for a formula
=IF(AN9<7,AM16,IF(AN9<10,AM17,IF(AN9<13,AM18,IF(AN9<15,AM19,IF(AN9>14,"Manager Appr")))))

I have seen and used a less intense one where all of the IFs are in the
first set of brackets and all of the results are in the second set. I cannot
find the same formula and cannot make it work on my own.

=IF(7,10,13,15,???) (results set)
 
R

Ron Coderre

Try something like this:

=IF(AN9>=15,"Manager Appr",LOOKUP(AN9,{0,7,10,13,15},AM16:AM19))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
J

jerminski73

PERFECT!! Thank you

This is the way I had alternatively found but I like your option better.

Jeremy
 
R

Ron Coderre

I'm glad I could help........(and thanks for the feedback.)

***********
Regards,
Ron

XL2002, WinXP
 
J

jerminski73

As I learn more, I create new problems to solve.... I am writing a "multiple
choice contract attached to the info you helped me fix. In it I want to put
a standard response based upon a numeric value entered on each line....

AZ14 has a pretyped roofing response, BA14 is a siding response, BB14 is a
Stone response, so if a 1 is entered then fill roofing response, 2=siding
response, 3=stone response.

If the value of AY14=1, AZ14 IF AY14=2, BA14 IF AY=3, BB14

I think you might get the picture. I am sure I am close to the answer but
am missing a comma or parenthesis or something... here's what I am trying to
use ...
=LOOKUP(AY14,{1,2,3,4,5,6,7,8,9,10},{AZ14,BA14,BB14,BC14,BD14,BE14,BF14,BG14,BH14,BI14})

Thanks
 
J

jerminski73

Thought I was close, This seems to be working, is this the best way to do it?

=IF(AY14=0,"",LOOKUP(AY14,{1,2,3,4},AZ14:BC14))
 
R

Ron Coderre

That would be fine.....

Alternatively, if the cell AY14 values are consecutive (beginning with zero)
AND the referenced Row_14 cells are also consecutive, you might consider this:

=IF(AY14=0,"",INDEX(AZ14:BC14,1,AY14))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Top