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)*S heet1!$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 ,
"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.