HLookup to scan upwards??

[loaiky]

See the attachment.

according to the grading scale on row1 and row2, give the grade of the scores on the lower column1 and fill it in the column2.....



I used the hlookup but failed cuz the hlookup scans downwards, but I need it to be scanned upwards..... I put "hlookup" and "dream results" for the comparison....

who can do a bit help?? other functions are also welcome!

many thanks!

[shg]

Why not change the threshold values so that they are correct, and then use HLOOKUP in the usual way?

What is the threshold score for each grade?

[Jonmo1]

Reverse the sort of the values in A1:I2
A1 = 9.999999999E+307 (largest number allowed in a cell)
B1 = 1500
C1 = 1300
etc
A2 = i
B2 = h
C2 = g
etc..

Then use
=INDEX($A$2:$I$2,MATCH(A8,$A$1:$I$1,-1))

[JieJenn]

Change your I1 from >1500 to 1501 then
in B8 enter =IFERROR(INDEX($A$2:$I$2,IF(MATCH(A8,$A$1:$I$1,1)+1>COUNT($A$1:$I$1),MATCH(A8,$A$1:$I$1,1),MATCH(A8,$A$1:$I$1,1)+1)),"a") and drag it down

[duanzhuanming]

Hi,
Another way....

=IF(A8>$H$1,$I$2,INDEX($A$2:$H$2,MATCH(MIN(IF($A$1:$H$1-A8>0,$A$1:$H$1,MAX($A$1:$H$1))),$A$1:$H$1,0)))

Confirmed with Ctrl+Shift+Enter!