Selecting values from adjacent rows

Hi,

I have 2 rows:
Row1: each column has the date (e.g. dd/mm)
Row2: each column has a numeric value e.g. 272)

I want to in another cell do the following.

a. Search Row1 to find a date equal to todays date.
b. Then select the matching value from Row2 and place that in my cell.
or if it cant find todays date then it should take the value from the previous cell (date).

ie. Row1: 04/11 05/11 06/11 07/11 09/11
Row2: 345 340 335 320 305

If todays 06/11 then it must return 335 to my cell. If today is 08/11 it must return 320 into my cell.

Please assist with this comulation.

Thanks

You could use HLOOKUP

=HLOOKUP(TODAY(),1:2,2)

or INDEX/MATCH

=INDEX(2:2,MATCH(TODAY(),1:1))

If you look at my HLOOKUP you will note I have a 2 row table_array (1:2), the first row (1) being that containing the dates (in which the criteria value will be searched for) and the second (2) being that containing the values of interest... the row_index_num (to determine from which row of the table_array the result should be extracted) is set to be 2, ie return the 2nd row of the range from the column in which the relevant date is found in the header row.

In your version of the same the HLOOKUP table_array consists of only one row of data namely the dates thus all that is being returned by the HLOOKUP is the date value itself which leads me on to...

It brings back a wird number

Dates in XL are integers, the number you're seeing is the Integer value of the date value returned from F5:R5.

An HLOOKUP as it turns out - will not work here anyway given it can only look downwards (in the same way a VLOOKUP can only look to the right), so you will need to revert to the INDEX match approach also illustrated in post # 2
(changing reference to row 2 to be F1:R1 and references to row 1 to be F5:R5)