Picking a value from a table using Column and Row ranges?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Dalton - 20 May 2008 09:50 GMT

I have a table that looks something like this:
100-76 51-75 26-50 0-25
$2.5m-$5m 6.02 6.65 7.28 7.28
$5m - $7m 5.47 6.03 6.59 7.17
$7.5m-$10m 5.21 5.78 6.39 6.99

Say in another worksheet (sheet2) in cell A1 I have a figure of $6m & next
to that in cell A2 I have a figure of 55, is it possible for excel to look at
those two numbers and using the column and row ranges pick out the correct
value from the table of 6.03???
Your help would be greatly appreciated.
Many thanks,

Reply to this Message

Bob Phillips - 20 May 2008 10:01 GMT

Reorganise the table like this

0 25 51 76
$2.5M 7.28 7.28 6.65 6.02
$5M 7.17 6.59 6.03 5.47
$7.5M 6.99 6.39 5.78 5.21

and use

=INDEX(Sheet1!B2:E4,MATCH(A1,Sheet1!A9:A11),MATCH(A2,Sheet1!B8:E8))

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have a table that looks something like this:
> 100-76 51-75 26-50 0-25
[quoted text clipped - 9 lines]
> Your help would be greatly appreciated.
> Many thanks,

Reply to this Message

James Dalton - 20 May 2008 23:02 GMT

Bob,
Thanks for this it is exactly what I needed! Your a star!
Cheers,

James Dalton.

> Reorganise the table like this
>
[quoted text clipped - 20 lines]
> > Your help would be greatly appreciated.
> > Many thanks,

Reply to this Message