Look up function
 

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

Here's one crack at it ..

Assuming the ref table below is in Sheet1, in B1:D6

....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 B1:D1
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 ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"neeses" wrote in message
...

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