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

  • Thread starter Thread starter worzell
  • Start date Start date
W

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
 
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)
 
One way where your dates are in row 18 and your values in row 23
=INDEX(23:23,0,MATCH(A17,18:18))
 
Back
Top