Excel 2003 - Returning the date in column for matching values

Hello,

I am using Excel 2003. I have attached a data file here. Need help in getting the values in Q3, R3 and S3.

Scenario:

Q1 has the number = 1. So I want the cell Q3 to return 2/11/2013 as that is the cell corresponding to the Item1 (value specified in P3) with the value 1(value specified in Q1) in the cell. Basically, I need the date corresponding to cell which has the value of Q1 for the value of P3.

Similarly, R2 must have the value 2/12/2013 and S3 must have the value 2/14/2013 returned.

Please help.

Something like:

Formula:

Please Login or Register  to view this content.

Regards, TMS

Hello SunRay,

This formula can be placed in cell Q3. You can drag this across and then down each column.
It will examine rows 3 through 6. the header row is set at row 2. If you expand the data then you will need to change row 6 to whatever the last row will be.

=IF(ISNA(INDEX($B$2:$K$2,1,MATCH(COLUMN()-16,$B3:$K3,0))),"",INDEX($B$2:$K$2,1,MATCH(COLUMN()-16,$B3:$K3,0)))

Hello SunRay,

#2 You can disregard this statement. I originally had a different formula.
#3 The COLUMN statement returns the column number of the cell the formula is in. The formula is in column "Q" or the 17th column on the worksheet. The days are 1,2,3, etc. This yields the a value eequal to the day being searched for.

Hello Leith Ross,

Thank you for the explanation, I now understand what COLUMN does in the formula.

Could you please help me with the #1 that i mentioned in my earlier reply (if the item# in column P is not in the order of the number, which is going to be the case in reality, it does not show the correct dates. (See the file attached)). Please see the 'data ver1 - need updates.xls' file that I have attached in my last response.
The formula should look up for the correct item# and then look up the day# that we need and return the date for that cell.

In reality, I have the data from column O in a different sheet as the date range can go on and on from column B to anything...

Hello SunRay,

Here is the updated formula and workbook.

=IF(ISERROR(OFFSET($A$2,0,MATCH(Q$1,OFFSET($B$3:$K$3,MATCH($P3,Item_Numbers,0)-1,0,1,10),0),1,1)),"",OFFSET($A$2,0,MATCH(Q$1,OFFSET($B$3:$K$3,MATCH($P3,Item_Numbers,0)-1,0,1,10),0),1,1))

Hello SunRay,

The table on Sheet2 nows uses the data on Sheet1.