How do I lookup values from rows and columns Simultaneously

[Guest]

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 B11 contains months i.e Jan-05, April-05, Aug-05 (being
datevalue labels). Cells B210 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.

 

[Guest]

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

 

[Domenic]

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!