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

PS Apologies to anyone I've offended by double posting - wasn't sure
which group this should go into.

 

[Bob Phillips]

=INDEX(A3:C7,5,MAX(1,MATCH(A1,A3:C3,0)-1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Max]

=OFFSET(HLOOKUP($A$1,$A$3:$C$7,5,FALSE),0,-1)

Try in D2: =OFFSET(A3,2,MATCH(A1,A3:C3,0)-2)

 

[Max]

Errata, it should read
In D2: =OFFSET(A3,4,MATCH(A1,A3:C3,0)-2)