PLEASE HELP OFFSET / HLOOKUP / MATCH I HAVE READ OTHER POSTS

[worzell]

Hi,

I have looked at loads of other posts around this but I can't get any
of the examples to fix this.

I have the following problem:

I have a date which I would like to use as a reference to lookup
data.

Data table looks like below:

A B C
1 29/02/2008
2
3 31/01/2008 29/02/2008 31/03/2008
4 R R R
5 R R R
6 R R R
7 F G H

In cell D1 I have the HLOOKUP formula which is looking up the date in
A1 in the table A3:C7 and returning row 5 below it. This works fine.

In cell D2 I would like to have a formula which is the same but
offsets by -1 column. I have tried
=OFFSET(HLOOKUP($A$1,$A$3:$C$7,5,FALSE),0,-1) but cannot for the life
of me get it to work!

I have also tried using MATCH & OFFSET but I am not very au fait with
with either and can't seem to get them to work!

Any suggestions greatly appreciated.

Many Thanks,

Worzell

 

[Roger Govier]

Hi

You can't use Offset in that way as Hlookup is returning you a value, not an
address
Try
=INDEX(A3:C7,5,MATCH(A1,A3:C3,0)-1)

 

[Don Guillett]

One way where your dates are in row 18 and your values in row 23
=INDEX(23:23,0,MATCH(A17,18:18))