lookup value within range

[goofy78270]

I was wondering if it was possible to lookup a value within a given range. I am pretty sure that an example will explain this better than my words.

A B C D
Class A 0 60 F
Class A 60.01 70 D
Class A 70.01 80 C
Class A 80.01 90 B
Class A 90.01 125 A
Class B 0 70 F
Class B 70.01 76 D
Class B 76.01 81 C
Class B 81.01 92 B
Class B 92.01 125 A

If a grade (68.23) is given in class A, the result would be a D but in class B, the result would be an F. I am looking to match the values in column A with the desired worksheet or column heading and then evaluate if a given grade is greater than Column B but less than Column C and return the value for Column D.

 

[tony80]

If the data in column A is sorted then the following formula should work:

=VLOOKUP(score,OFFSET($B$1,MATCH("CLASS A",$A:$A,0)-1,0,COUNTIF($A:$A,"CLASS A"),3),3)

 

[goofy78270]

If I wanted to insert another column as column A to the table such as grade 10, grade 11, would it be possible to add that to the lookup or would this be out of excels formula capability? Is there a max to the number of additional columns that could be added?


Similar Post: http://www.mrexcel.com/forum/showthread.php?t=540063

 

[T. Valko]

I was wondering if it was possible to lookup a value within a given range. I am pretty sure that an example will explain this better than my words.

A B C D
Class A 0 60 F
Class A 60.01 70 D
Class A 70.01 80 C
Class A 80.01 90 B
Class A 90.01 125 A
Class B 0 70 F
Class B 70.01 76 D
Class B 76.01 81 C
Class B 81.01 92 B
Class B 92.01 125 A

If a grade (68.23) is given in class A, the result would be a D but in class B, the result would be an F. I am looking to match the values in column A with the desired worksheet or column heading and then evaluate if a given grade is greater than Column B but less than Column C and return the value for Column D.

Try this...

With that table in the range A2:D11...

Lookup values:

• F2 = Class A
• G2 = 68.23

Then, this array formula**:

=INDEX(D2:D11,MATCH(1,IF(A2:A11=F2,IF(G2>=B2:B11,IF(G2<=C2:C11,1))),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[T. Valko]

If I wanted to insert another column as column A to the table such as grade 10, grade 11, would it be possible to add that to the lookup or would this be out of excels formula capability? Is there a max to the number of additional columns that could be added?


Similar Post: http://www.mrexcel.com/forum/showthread.php?t=540063

If I understand then you would just add another test like this:

Array entered**:

"Pseudo code"

=INDEX(range,MATCH(1,IF(range=grade,IF(range=class,IF(number>=range,IF(number<=range,1)))),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.