On Sheet1, I need to lookup a text value on column A, and another value on
cell $c$4 (the last value entered by user once a week, i.e. 01, 04, 11, etc).
On Sheet2 I have the same text values on column A. However the second lookup
value ($C$4) has to match values on row 5 between (AF5:AR5) on sheet2. when
the two values matches those from sheet1, the formula should return the value
corresponding to ColA and row5. I'm looking up columns and rows at the same
time.

Example: If my 1st lookup value is "TEXT" located on A25 sheet1 & sheet2,
and my 2nd lookup value is 11 on $C$4 sheet1, the formula should return the
value on AP25 from sheet2.

Any direction is greatly appreciated.

Thanks.
--
when u change the way u look @ things, the things u look at change.

I have tried INDEX/MATCH, but it didn't work either. Here's my formula:

INDEX('SHEET2!$A$6:$A$200,MATCH('SHEET1'!$A15,'SHE ET2'!$A$6:$A$200,0),MATCH('SHEET1'!$C$4,'SHEET2'!$ AF$6:$AR$6,0)).

Sometimes I get a wrong value, other times, I get the descriptive text
instead of a numerical value!.
--
when u change the way u look @ things, the things u look at change.


"sahafi" wrote:

On Sheet1, I need to lookup a text value on column A, and another value on
cell $c$4 (the last value entered by user once a week, i.e. 01, 04, 11, etc).
On Sheet2 I have the same text values on column A. However the second lookup
value ($C$4) has to match values on row 5 between (AF5:AR5) on sheet2. when
the two values matches those from sheet1, the formula should return the value
corresponding to ColA and row5. I'm looking up columns and rows at the same
time.

Example: If my 1st lookup value is "TEXT" located on A25 sheet1 & sheet2,
and my 2nd lookup value is 11 on $C$4 sheet1, the formula should return the
value on AP25 from sheet2.

Any direction is greatly appreciated.

Thanks.
--
when u change the way u look @ things, the things u look at change.