Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE HELP OFFSET / HLOOKUP / MATCH I HAVE READ OTHER POSTS
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE HELP OFFSET / HLOOKUP / MATCH I HAVE READ OTHER POSTS
=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) wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE HELP OFFSET / HLOOKUP / MATCH I HAVE READ OTHER POSTS
=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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE HELP OFFSET / HLOOKUP / MATCH I HAVE READ OTHER POSTS
Errata, it should read
In D2: =OFFSET(A3,4,MATCH(A1,A3:C3,0)-2) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to read own posts or replied Posts | Excel Discussion (Misc queries) | |||
HLOOKUP and OFFSET - Any Suggestions | Excel Worksheet Functions | |||
Interpolate, Interpolation, VlookUp, HlookUp, Read a table | Excel Worksheet Functions | |||
Does anyone have a solution for nesting an OFFSET in an HLOOKUP? | Excel Worksheet Functions | |||
When MATCH and v/hLOOKUP functions *FAIL* to match (but they should)... | Excel Worksheet Functions |