I’m using HLOOKUP and want to drag down the formula for multiple rows. The row_index_number therefore is wrong after the first row (the fifth row becomes the fourth). Is there specific the precise row to return answers from?

In this example, is there a way to specify row 12, or to automatically alter the ‘9’ to an ‘8’, then a ‘7’ etc as the formula is dragged down?

If you prefix the cell reference in a formula with $ then it fixes it.

Hence if you always want Column I, but the row to change, instead of I12, use $I12

If you want to fix row, but allow column to change – I$12

If you want to absolutely fix the cell, $I$12

That doesn’t work in this case as the row_index_number specifies which row you want in the array, not which row in the sheet. Unless i’m misunderstanding you.

In your example, in row j, run the numbers down in a list: ‘9’ in J4, ‘8’ in J5 etc.

Then HLOOKUP(blahblah,J4,false) for your first one. As you pull the formula down it will reference J5, then J6 etc.

Not particularly elegant, but quick and easy. The index doesn’t need to be adjacent to the table, btw, can be stashed away in a hidden column, on another sheet etc if needed.

edlong – Member
In your example, in row j, run the numbers down in a list: ‘9’ in J4, ‘8’ in J5 etc.

Then HLOOKUP(blahblah,J4,false) for your first one. As you pull the formula down it will reference J5, then J6 etc.

Not particularly elegant, but quick and easy. The index doesn’t need to be adjacent to the table, btw, can be stashed away in a hidden column, on another sheet etc if needed.

Genius! Thankyou!

or replaces number ROWS($A:A) and that copies down

EDIT:Actually 10-ROWS($D:D)

You could have also just filled the bottom row down 9 times and used your original formula, but again that’s a little bit ugly.

=HLOOKUP(“YES”,D4:I$12,COUNT(D4:I$12),FALSE)

That’s definitely more graceful. Does it all in the formula and doesn’t require you to mucky the sheet with anything else. Allows fill down and will reduce the count as you move down.