HLOOKUP using variable columns
 

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).

HLOOKUP using variable columns
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Chuka" wrote in message
...
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).

HLOOKUP using variable columns
 

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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Chuka" wrote in message
...
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).

HLOOKUP using variable columns
 

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 B6:D7 the only possible values are 1, and 2; i.e. the
first or second row of B6:D7
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Chuka" wrote in message
...
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" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Chuka" wrote in message
...
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).

HLOOKUP using variable columns
 

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


"Chuka" wrote:

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).

HLOOKUP using variable columns
 

Hear, here, Shane ! <gr
--
Bernard

"Shane Devenshire" wrote in
message ...
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


"Chuka" wrote:

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).