HLOOKUP using variable columns

[Chuka]

I'm trying to reference a variable column rather than a specific column # in
my HLOOKUP formula.


A1 B1 C1 D1
A2 1/6/2009 Today's Date
A3 1/6/2009 Start Date
A4
A5 1/5/2009 1/6/2009
A6 1/5/2009 1 0
A7 1/6/2009 2 1


I would write my formula as =HLOOKUP(B3,$B$6:$D$7,?,FALSE) In place of the
? I've tried to reference a cell that contained a whole number but that didnt
work. I want to be able to copy and paste this formula for several different
individuals based upon their starting date & not necess. column D every time.
Any ideas here? Please use literal interpretation (A6=A6,B6=1/6/2009,C6=2).

 

[Bernard Liengme]

I am confused! You are using HLOOKUP but talk about a variable COLUMN
Is this a VLOOKUP or is it the ROW that varies?
It is perfectly OK to use a cell reference rather than a number literal in
the syntax of either function
I would help if we had a better idea of the actual data.
best wishes

 

[Chuka]

Thanks for your timely response. Yes the row varies based upon the date.
How do I write a formula for a variable row? Sorry I think I got my VLOOKUP
& HLOOKUP mixed. When I attempted to reference a cell
=HLOOKUP(B3,$B$6:$D$7,?,FALSE) I had trouble. Any ideas how to correct this
without including a whole number for the row?

 

[Bernard Liengme]

You should be able to use =HLOOKUP(B3,$B$6:$D$7,D5,FALSE)
with D5 holding a valid row number.
Since the range is B67 the only possible values are 1, and 2; i.e. the
first or second row of B67

 

[Shane Devenshire]

Hi,

The real key hear is to decide on how you want to determine the varialble
row number. You say you want it to be a variable, but you don't tell us what
determines what the row number should be?

Thanks,
Shane

 

[Bernard Liengme]

Hear, here, Shane ! <gr>
--
Bernard

Hi,

The real key hear is to decide on how you want to determine the varialble
row number. You say you want it to be a variable, but you don't tell us
what
determines what the row number should be?

Thanks,
Shane
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire