Look up function

[Guest]

Grade 1 2 3, etc.
10,000 10,500 11,000
12,000 12,500 13,000
14,000 14,500 15,000
16,000 16,500 17,000
18,000 18,500 19,000
We have a worksheet that lists numbers in rows and columns. At the top of
each column is a number that represents a grade. What function would we use
to look up the value of a cell from another sheet, compare it to this table,
and return the column “grade†number? Example, 12,500 should return grade 2.

Thank you for your time and cooperation.

S. Neese

 

[Max]

Here's one crack at it ..

Assuming the ref table below is in Sheet1, in B16

....1...........2............3
10,000 10,500 11,000
12,000 12,500 13,000
14,000 14,500 15,000
16,000 16,500 17,000
18,000 18,500 19,000

In Sheet2
-------------
Assuming the values: 12500, 17000, 16000 .. etc
are in col A, A2 down

Put in B2:

=IF(ISNA(MATCH($A2,OFFSET(Sheet1!$B$1:$D$1,MATCH(1,MMULT(--(Sheet1!$B$2:$D$6
=$A2),{1;1;1}),0),),0)),"",INDEX(Sheet1!$B$1:$D$1,MATCH($A2,OFFSET(Sheet1!$B
$1:$D$1,MATCH(1,MMULT(--(Sheet1!$B$2:$D$6=$A2),{1;1;1}),0),),0)))

(a normal ENTER will do)

Copy B2 down

Col B will return the column "grade" numbers (i.e. 1 or 2 or 3 - from B11
in Sheet1) corresponding to the values in col A from the ref table in
Sheet1. Unmatched values in col A, if any, will return blanks: ""

Adapt to suit ..