If (and) nesting

[Ron Purpura]

Hello All
I am attempting to create a scheduling tool utilizing Excel 2010. It is very possible I am getting too fancy with this formula, but would greatly appreciate any feedback as to why I continue to get an #VALUE Error in some cells. Basically, I am trying to assign coverage of different medical disciplines in a clinic based upon the sum of 2 cells (I37 and I38). Cell I39 is a simple formula, which works properly, but I am finding that as I add just 1 more statements(In the exact same way) I am getting the #VALUE error in cells I40,I41 and I42. I have attached the file as it would be difficult to describe.
As always, thanks to all

[BadlySpelledBuoy]

If you use the below formula in cell I40, do you get the answer you're expecting?

=VLOOKUP(SUM(I37:I38),Q36:T46,4,TRUE)

[Ace_XL]

In I39
=LOOKUP(SUM($I$37:$I$38),$Q$36:$S$46)

In I40
=LOOKUP(SUM($I$37:$I$38),$Q$36:$T$46)

In I41
=LOOKUP(SUM($I$37:$I$38),$Q$36:$U$46)

In I42
=LOOKUP(SUM($I$37:$I$38),$Q$36:$V$46)

[Jonmo1]

Try a lookup instead of all the IF ANDs

For I39
=LOOKUP(SUM($I$37:$I$38),$Q$36:$Q$46,$S$36:$S$46)

Change the S's to T's for I 40, U for I41 and V for I42.

[Ron Purpura]

Thank you all !

[Jonmo1]

You're welcome