Finding cell by headings

[Mike F]

I need to figure out a formula that will allow me to find
a value in a table. For example. I have a table that is
4 colums and 38 rows.
Formatted like so:

1 1-3 4-10 11+
40
39
38
37
..
..
..
..
And down the rows and colums each cell has a different
accrual rate. I need to take the info from two cells on
another sheet and using the values (ex. 40 hrs and 1-3
yrs of service) have the formula find the correct
accrual rate. Any help would be GREATLY APPRECIATED!!
thanks.

 

[Frank Kabel]

Hi
change your headings to show only the lower boundary of your range.
e.g. change '1-3' to '1'

After this use the following formula
=INDEX(A1:E10,MATCH(40,A1:A10,0),MATCH(2,A1:E1,1))
to get the value for 2 service years and 40 hours

 

[Peo Sjoblom]

One way, assuming that you table is called MyTable (if you name it that,
this formula will work unaltered)

=INDEX(MyTable,MATCH(40,INDEX(MyTable,,1),0),MATCH("1-3",INDEX(MyTable,1,),0
))

note that I assume the top row is text formatted and not pseudo dates thus
the quotations

For better usability change the lookup value in MATCH to cell references
where you type in the
values (note that text format in the table need text format in the lookup
cell)

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Guest]

Thank both of you, this is exactly what I was looking for.