Variable Row Numbers When Dragging HLOOKUP

fargone · General User Joined: 18 Apr 2006 Posts: 13

=IF(ISNA(HLOOKUP($C$2;$PRICE.$C$1:$O$36;2;0));"NOT STANDARD";HLOOKUP($C$2;$PRICE.$C$1:$O$36;2;0))

How do I get the row number, '2' in this case, to change to 3, 4, 5...... etc when I copy the formula into multiple cells of a column?

I know I can go through and manually change them but that is just asking for human error plus I'm lazy.

french · Posted: Thu May 11, 2006 4:57 am Post subject:

Delete the dollar sign ($) from in front of the row number in the formula.

RickRandom · Super User Joined: 27 Jan 2006 Posts: 1082 Location: UK

I have usually had to put a column of 2, 3, 4, etc next to where the LOOKUP function is going, then in the LOOKUP refer (as a relative address) to the cell with the 2 in it instead of putting 2 in. Then fill down.

I don't know of a way to fully automate it.

Villeroy · Posted: Thu May 11, 2006 8:46 am Post subject:

=IF(ISNA(HLOOKUP($C$2;$PRICE.$C$1:$O$36;ROW($IV2);0));"NOT STANDARD";HLOOKUP($C$2;$PRICE.$C$1:$O$36;ROW($IV2);0))
I use column IV, hoping that it is not involved (avoiding circular reference). You may use $OtherSheet.$A2 as well.

fargone · General User Joined: 18 Apr 2006 Posts: 13

Thanks everyone. Once again the support for Ooo is super.

Substituting ROW($IV2) worked like a charm but I'm kind of fuzzy on why it works?

What does the $IV mean? When I am helping my daughter with her school work and she asks me why I did this I don't want to say "Do not question the Mighty Villeroy. Just do what he says."

Villeroy · Posted: Fri May 12, 2006 1:09 am Post subject:

I replaced the bold 2 in your initial posting with something that returnes 2 in the first row and consecutive row-numbers in case it's copied down wards.
See help on row. The missing $ does exactly the same as french suggested for your $C$2 -> $C2. When you copy up or down a formula, having relative row-references (missing $ before row-numbers) the row-numbers adjust to their new position. Same with columns references, missing $ and copy direction left or right.
Simply use some free cell-range and copy around some formulas ROW(), COLUMN(), ROW(X123), COLUMN($X123),ROW($X$123),...

fargone · General User Joined: 18 Apr 2006 Posts: 13

I did play around with =ROW($IV2) and now see that the $IV can be $ANY_COLUMN_LETTER as long as it does not refer to a column that is actually in use in the spreadsheet.

Now I have to go back and rework all my previous spreadsheet templates and make them function more efficiently. Made myself more work. A little knowledge IS dangerous.

Thanks again everyone.