Apologies if this covered - cannot find.
I believe that the row_index_num in HLOOKUP will never adjust as the formula
is copied down but users in our Finance dept are all insisting they have been
using HLOOKUP for years and the row index has always adjusted as the formula
is copied down a column but no longer does and want IT to fix. An example
formula is
=HLOOKUP($A$1,'Budget for year'!$A$1:$M$399,3,FALSE)
Where $A$1 is a value in the first row of the lookup table which finds the
month they are interested in. They have cost codes in the first col of the
lookuptable which are duplicated down the first col in the sheet they are
using the lookup in. What they want and claim has always happened in the
past is for the row index to adjust as the formula is copied down, the
example row index changing to 4, 5 , 6 etc so the figure for the correct code
is displayed. We can achieve this by using a function to determine the
row_index, but they insist they have been using HLOOKUP for years and have
never had to do so. Although I think that is not possible, I must be wrong
as they are all so adamant. What am I missing? Is there some setting that
can be changed to force the rowindexnum to act relatively?
Many thanks

Maybe I'm missing something - (wouldn't be the first time), but it
seems like there is some confusion between an index value and a lookup
value.

The normal use of lookup tables is to have a fixed offset. In the case
of a Vlookup, a column offset and in an Hlookup a row offset.

Your use of the phrase

" the example row index changing to 4, 5 , 6 etc so the figure for the
correct code is displayed."

seems to suggest that what you're describing is a Vlookup, where you
are looking for a cost code in a vertical column and wanting to find
the 'figure for the correct code' in another column on the same row as
the found value.

Perhaps it would help if you post a specific example

Rgds

On Fri, 25 Aug 2006 02:47:02 -0700, CarolineJ
wrote:

Apologies if this covered - cannot find.
I believe that the row_index_num in HLOOKUP will never adjust as the formula
is copied down but users in our Finance dept are all insisting they have been
using HLOOKUP for years and the row index has always adjusted as the formula
is copied down a column but no longer does and want IT to fix. An example
formula is
=HLOOKUP($A$1,'Budget for year'!$A$1:$M$399,3,FALSE)
Where $A$1 is a value in the first row of the lookup table which finds the
month they are interested in. They have cost codes in the first col of the
lookuptable which are duplicated down the first col in the sheet they are
using the lookup in. What they want and claim has always happened in the
past is for the row index to adjust as the formula is copied down, the
example row index changing to 4, 5 , 6 etc so the figure for the correct code
is displayed. We can achieve this by using a function to determine the
row_index, but they insist they have been using HLOOKUP for years and have
never had to do so. Although I think that is not possible, I must be wrong
as they are all so adamant. What am I missing? Is there some setting that
can be changed to force the rowindexnum to act relatively?
Many thanks

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Thanks Richard - you pretty much confirm what I thought
I believe like you that the index is always static - just thought I ought to
double check as there are lots of excerienced Excel users in our finance dept
all convinced that I am wrong, and they are on the defensive because they
feel I am calling them idiots when I say that what they insist has worked for
years never could have in the way they specify.

Instead of using hlookup I would use a vlookup then the column_index would
specify the column relevant to the month of interest. What our finance team
do is use hlookup so that they only have to change the month in one cell and
the whole thing changes to report on that months column - rather than having
to change the column_index in hundreds of rows if they used a vlookup. I
don't think it ever worked as simply as they think - ie that in an hlookup
the specified rowindex number adjusted as the formula was copied down - just
wanted to give them the benefit of doubt and double check.

"Richard Buttrey" wrote:

Maybe I'm missing something - (wouldn't be the first time), but it
seems like there is some confusion between an index value and a lookup
value.

The normal use of lookup tables is to have a fixed offset. In the case
of a Vlookup, a column offset and in an Hlookup a row offset.

Your use of the phrase

" the example row index changing to 4, 5 , 6 etc so the figure for the
correct code is displayed."

seems to suggest that what you're describing is a Vlookup, where you
are looking for a cost code in a vertical column and wanting to find
the 'figure for the correct code' in another column on the same row as
the found value.

Perhaps it would help if you post a specific example

Rgds

On Fri, 25 Aug 2006 02:47:02 -0700, CarolineJ
wrote:

Apologies if this covered - cannot find.
I believe that the row_index_num in HLOOKUP will never adjust as the formula
is copied down but users in our Finance dept are all insisting they have been
using HLOOKUP for years and the row index has always adjusted as the formula
is copied down a column but no longer does and want IT to fix. An example
formula is
=HLOOKUP($A$1,'Budget for year'!$A$1:$M$399,3,FALSE)
Where $A$1 is a value in the first row of the lookup table which finds the
month they are interested in. They have cost codes in the first col of the
lookuptable which are duplicated down the first col in the sheet they are
using the lookup in. What they want and claim has always happened in the
past is for the row index to adjust as the formula is copied down, the
example row index changing to 4, 5 , 6 etc so the figure for the correct code
is displayed. We can achieve this by using a function to determine the
row_index, but they insist they have been using HLOOKUP for years and have
never had to do so. Although I think that is not possible, I must be wrong
as they are all so adamant. What am I missing? Is there some setting that
can be changed to force the rowindexnum to act relatively?
Many thanks

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

In which case all they need to do is change the month in the cell
they're already using.
In a second cell use
=Match(monthcell,columnheadingrange)
This will identify the relevant column.

So with A1=March,
B3:D3 = Jan, Feb, March
B1 is =Match(A1,A3:D3)

A4:A6 = account codes
B4:D6 = data

A10:A?? = cost codes
B10 is =VLOOKUP(A10,$A$3:$D$6,$B$1,FALSE)
and copy down col A.

HTH


On Fri, 25 Aug 2006 04:08:02 -0700, CarolineJ
wrote:

Thanks Richard - you pretty much confirm what I thought
I believe like you that the index is always static - just thought I ought to
double check as there are lots of excerienced Excel users in our finance dept
all convinced that I am wrong, and they are on the defensive because they
feel I am calling them idiots when I say that what they insist has worked for
years never could have in the way they specify.

Instead of using hlookup I would use a vlookup then the column_index would
specify the column relevant to the month of interest. What our finance team
do is use hlookup so that they only have to change the month in one cell and
the whole thing changes to report on that months column - rather than having
to change the column_index in hundreds of rows if they used a vlookup. I
don't think it ever worked as simply as they think - ie that in an hlookup
the specified rowindex number adjusted as the formula was copied down - just
wanted to give them the benefit of doubt and double check.

"Richard Buttrey" wrote:

Maybe I'm missing something - (wouldn't be the first time), but it
seems like there is some confusion between an index value and a lookup
value.

The normal use of lookup tables is to have a fixed offset. In the case
of a Vlookup, a column offset and in an Hlookup a row offset.

Your use of the phrase

" the example row index changing to 4, 5 , 6 etc so the figure for the
correct code is displayed."

seems to suggest that what you're describing is a Vlookup, where you
are looking for a cost code in a vertical column and wanting to find
the 'figure for the correct code' in another column on the same row as
the found value.

Perhaps it would help if you post a specific example

Rgds

On Fri, 25 Aug 2006 02:47:02 -0700, CarolineJ
wrote:

Apologies if this covered - cannot find.
I believe that the row_index_num in HLOOKUP will never adjust as the formula
is copied down but users in our Finance dept are all insisting they have been
using HLOOKUP for years and the row index has always adjusted as the formula
is copied down a column but no longer does and want IT to fix. An example
formula is
=HLOOKUP($A$1,'Budget for year'!$A$1:$M$399,3,FALSE)
Where $A$1 is a value in the first row of the lookup table which finds the
month they are interested in. They have cost codes in the first col of the
lookuptable which are duplicated down the first col in the sheet they are
using the lookup in. What they want and claim has always happened in the
past is for the row index to adjust as the formula is copied down, the
example row index changing to 4, 5 , 6 etc so the figure for the correct code
is displayed. We can achieve this by using a function to determine the
row_index, but they insist they have been using HLOOKUP for years and have
never had to do so. Although I think that is not possible, I must be wrong
as they are all so adamant. What am I missing? Is there some setting that
can be changed to force the rowindexnum to act relatively?
Many thanks

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

or use match directly in the vlookup or hlookup to find the row or column
you want
--
paul

remove nospam for email addy!



"Richard Buttrey" wrote:

In which case all they need to do is change the month in the cell
they're already using.
In a second cell use
=Match(monthcell,columnheadingrange)
This will identify the relevant column.

So with A1=March,
B3:D3 = Jan, Feb, March
B1 is =Match(A1,A3:D3)

A4:A6 = account codes
B4:D6 = data

A10:A?? = cost codes
B10 is =VLOOKUP(A10,$A$3:$D$6,$B$1,FALSE)
and copy down col A.

HTH


On Fri, 25 Aug 2006 04:08:02 -0700, CarolineJ
wrote:

Thanks Richard - you pretty much confirm what I thought
I believe like you that the index is always static - just thought I ought to
double check as there are lots of excerienced Excel users in our finance dept
all convinced that I am wrong, and they are on the defensive because they
feel I am calling them idiots when I say that what they insist has worked for
years never could have in the way they specify.

Instead of using hlookup I would use a vlookup then the column_index would
specify the column relevant to the month of interest. What our finance team
do is use hlookup so that they only have to change the month in one cell and
the whole thing changes to report on that months column - rather than having
to change the column_index in hundreds of rows if they used a vlookup. I
don't think it ever worked as simply as they think - ie that in an hlookup
the specified rowindex number adjusted as the formula was copied down - just
wanted to give them the benefit of doubt and double check.

"Richard Buttrey" wrote:

Maybe I'm missing something - (wouldn't be the first time), but it
seems like there is some confusion between an index value and a lookup
value.

The normal use of lookup tables is to have a fixed offset. In the case
of a Vlookup, a column offset and in an Hlookup a row offset.

Your use of the phrase

" the example row index changing to 4, 5 , 6 etc so the figure for the
correct code is displayed."

seems to suggest that what you're describing is a Vlookup, where you
are looking for a cost code in a vertical column and wanting to find
the 'figure for the correct code' in another column on the same row as
the found value.

Perhaps it would help if you post a specific example

Rgds

On Fri, 25 Aug 2006 02:47:02 -0700, CarolineJ
wrote:

Apologies if this covered - cannot find.
I believe that the row_index_num in HLOOKUP will never adjust as the formula
is copied down but users in our Finance dept are all insisting they have been
using HLOOKUP for years and the row index has always adjusted as the formula
is copied down a column but no longer does and want IT to fix. An example
formula is
=HLOOKUP($A$1,'Budget for year'!$A$1:$M$399,3,FALSE)
Where $A$1 is a value in the first row of the lookup table which finds the
month they are interested in. They have cost codes in the first col of the
lookuptable which are duplicated down the first col in the sheet they are
using the lookup in. What they want and claim has always happened in the
past is for the row index to adjust as the formula is copied down, the
example row index changing to 4, 5 , 6 etc so the figure for the correct code
is displayed. We can achieve this by using a function to determine the
row_index, but they insist they have been using HLOOKUP for years and have
never had to do so. Although I think that is not possible, I must be wrong
as they are all so adamant. What am I missing? Is there some setting that
can be changed to force the rowindexnum to act relatively?
Many thanks

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Fri, 25 Aug 2006 05:14:02 -0700, paul
wrote:

or use match directly in the vlookup or hlookup to find the row or column
you want

Indeed.

Matter of personal preference I suppose. I tend to break formula down
in a case like this.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

my formulas are broken down on monday mornings.By friday I can string a few
functions together<g
paul

remove nospam for email addy!



"Richard Buttrey" wrote:

On Fri, 25 Aug 2006 05:14:02 -0700, paul
wrote:

or use match directly in the vlookup or hlookup to find the row or column
you want

Indeed.

Matter of personal preference I suppose. I tend to break formula down
in a case like this.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________