Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can the Row_Index_Num in HLOOKUP adjust relatively?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can the Row_Index_Num in HLOOKUP adjust relatively?
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 __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can the Row_Index_Num in HLOOKUP adjust relatively?
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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can the Row_Index_Num in HLOOKUP adjust relatively?
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 __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can the Row_Index_Num in HLOOKUP adjust relatively?
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can the Row_Index_Num in HLOOKUP adjust relatively?
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 __________________________ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can the Row_Index_Num in HLOOKUP adjust relatively?
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup and hlookup | Excel Worksheet Functions | |||
Index / Hlookup | Excel Worksheet Functions | |||
Need help with HLOOKUP and MATCH functions | Excel Worksheet Functions | |||
auto adjust columns in a pivot | Excel Worksheet Functions | |||
automatically adjust row height in excel 2000 | Excel Discussion (Misc queries) |