Changing row reference +1 in HLOOKUP when date changes

[cmclux]

I want to do a HLOOKUP on a specific value in a sheet and reference to a specific row based on the date. Once a month has passed I want the row to increase by one so it takes the value for the following month.

 

[Jon von der Heyden]

Can you post a sample of your sheet or describe your sheet to us please?

 

[Jonmo1]

You can use the Month of that date as your row reference..

=HLOOKUP(A1,B1:F13,MONTH(A2)+1,0)

A2 is your date.

 

[cmclux]

The sheet has the following rows

Name Value


The HLOOKUP function is in the value column and searches another worksheet for the value of the specific name, in a specific row.

This is the formula I am using =HLOOKUP(A3,'SOURCE SHEET',17,FALSE) where A3 references to the name column.

The value in the source sheet changes on a monthly basis and has to be updated in the sheet described above. Right now I want it to show the value from the source sheet for the name for 30 April 2011, but next month it will have to show the value of 31 May 2011.

The old value does not have to be kept in the destination sheet.

Thank you for your help.