How do I lookup values from rows and columns Simultaneously

Am trying to create a fomulae that will look up data dependant on rows and
columns. E.g In the lookup range cells A1:A10 contains diffrent item codes
and columns B1:D1 contains months i.e Jan-05, April-05, Aug-05 (being
datevalue labels). Cells B2:D10 contain the data dependant e.g rates.

On the output sheet which contains item codes (A1:A10) and B1:M1 the months
of the year (Jan to Dec-05). For cells B1:M10 it should be pick rates for the
months that are less or equal to the column month (B1:M1) and itemcode as per
column A.

I am guessing there is a better way

=INDEX(Sheet1!$B$2:$D$10,MATCH($A2,Sheet1!$A$2:$A$10),MATCH(SUMPRODUCT(MAX((Sheet1!$B$1:$D$1<=B$1)*Sheet1!$B$1:$D$1)),Sheet1!$B$1:$D$1,0))

HTH
Lance

"PK" wrote:

> Am trying to create a fomulae that will look up data dependant on rows and
> columns. E.g In the lookup range cells A1:A10 contains diffrent item codes
> and columns B1:D1 contains months i.e Jan-05, April-05, Aug-05 (being
> datevalue labels). Cells B2:D10 contain the data dependant e.g rates.
>
> On the output sheet which contains item codes (A1:A10) and B1:M1 the months
> of the year (Jan to Dec-05). For cells B1:M10 it should be pick rates for the
> months that are less or equal to the column month (B1:M1) and itemcode as per
> column A.

Assuming that Sheet1 contains your source data, and Sheet2 contains your
output...

On Sheet2, enter the following formula in B2, copied across and down:

=INDEX(Sheet1!$B$2:$D$10,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$10,0),MATCH(Shee
t2!B$1,Sheet1!$B$1:$D$1))

Hope this helps!

In article <[email protected]>,
"PK" <[email protected]> wrote:

> Am trying to create a fomulae that will look up data dependant on rows and
> columns. E.g In the lookup range cells A1:A10 contains diffrent item codes
> and columns B1:D1 contains months i.e Jan-05, April-05, Aug-05 (being
> datevalue labels). Cells B2:D10 contain the data dependant e.g rates.
>
> On the output sheet which contains item codes (A1:A10) and B1:M1 the months
> of the year (Jan to Dec-05). For cells B1:M10 it should be pick rates for the
> months that are less or equal to the column month (B1:M1) and itemcode as per
> column A.