I am getting all of my "ifs", "ands" & "Vlookups" real confused on this one.
Any help is appreciated.
Here is what I would like to be able to do in J20 in sentence format:
[1] Find the value of H20 in A2:A11 (it will always be one of these values)
and use that row in the following
[2] If I20 >= value of Column C in the row found, Return "Superior" else
[3] IF I20 >= value of Column B in the row found, Return "Minimum" else
[4] Return "Remediate"
A B C
1 Min Sup
2 11 38 48
3 12 29 39
4 13 23 33
5 14 20 30
6 15 19 29
7 21 18 23
8 22 11 16
9 23 5 10
10 24 5 10
11 25 5 10
I hope my explanation makes sense. My head hurts. Thanks
Jason Lepack - 08 Feb 2007 21:07 GMT
On Feb 8, 3:34 pm, Bigfoot17 <Bigfoo...@discussions.microsoft.com>
wrote:
> I am getting all of my "ifs", "ands" & "Vlookups" real confused on this one.
> Any help is appreciated.
[quoted text clipped - 20 lines]
>
> I hope my explanation makes sense. My head hurts. Thanks
This should do the trick:
=IF(I20>=VLOOKUP(H20,A:C,3,FALSE),"Superior",IF(I20>=VLOOKUP(H20,A:C,
2,FALSE),"Minimum","Remediate"))
Cheers,
Jason Lepack
Bigfoot17 - 09 Feb 2007 15:20 GMT
Ya gotta love this group! It took me longer to write the question than to
get the response! Thanks.
> On Feb 8, 3:34 pm, Bigfoot17 <Bigfoo...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 30 lines]
> Cheers,
> Jason Lepack
JMB - 09 Feb 2007 02:19 GMT
Not any shorter, but just another way to look at it
=CHOOSE(SUMPRODUCT(--(I20>=VLOOKUP(H20,A:C,{2,3},FALSE)))+1,"Remediate","Minimum","Superior")
> I am getting all of my "ifs", "ands" & "Vlookups" real confused on this one.
> Any help is appreciated.
[quoted text clipped - 20 lines]
>
> I hope my explanation makes sense. My head hurts. Thanks