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