Copying HLookup Formula

[Joan]

Hi

I have the following function entered in a spreadsheet, and it works fine

=HLOOKUP($M$3,$Q$5:$DO$200,3,FALSE

My problem is when I try to copy the formula down to subsequent rows in the column. The row index number (3) remains the same, but I need it to increase by 1 for each row as I copy down so that the data will pull from the correct row. For example, the formula as shown above is in cell M7. When I copy the formula down to cell M8, I need the row index number to change to 4.

I don't use the lookup function very often, so I'd appreciate any suggestions

Thanks
Joan

 

[Frank Kabel]

Hi
use
=HLOOKUP($M3,$Q$5:$DO$200,3,FALSE)


--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

I have the following function entered in a spreadsheet, and it works fine.

=HLOOKUP($M$3,$Q$5:$DO$200,3,FALSE)

My problem is when I try to copy the formula down to subsequent rows

in the column. The row index number (3) remains the same, but I need
it to increase by 1 for each row as I copy down so that the data will
pull from the correct row. For example, the formula as shown above is
in cell M7. When I copy the formula down to cell M8, I need the row
index number to change to 4.

 

[Joan]

Hi Frank
Your suggestion changes the lookup value (M3 becomes M4), but the row index number (the second '3', the one before 'false') remains the same. I need to keep the 'M3' the same (hence the $M$3) but have the second '3' change to the subsequent number
Thanks
Joa

----- Frank Kabel wrote: ----

H
us
=HLOOKUP($M3,$Q$5:$DO$200,3,FALSE


-
Regard
Frank Kabe
Frankfurt, German

Hi

in the column. The row index number (3) remains the same, but I nee
it to increase by 1 for each row as I copy down so that the data wil
pull from the correct row. For example, the formula as shown above i
in cell M7. When I copy the formula down to cell M8, I need the ro
index number to change to 4

 

[Frank Kabel]

Hi
sorry, my fault. Misread your question. Try
=HLOOKUP($M$3,$Q$5:$DO$200,ROW(3:3),FALSE)

and copy down

-----Original Message-----
Hi Frank,
Your suggestion changes the lookup value (M3 becomes M4),

but the row index number (the second '3', the one
before 'false') remains the same. I need to keep the 'M3'
the same (hence the $M$3) but have the second '3' change
to the subsequent number.

 

[Joan]

Thanks, Frank. This worked to solve the problem I posted. Now, though, if I delete a row in the spreadsheet, the formula does not automatically adjust (it stays the same). I can manually re-copy the formula every time I have to delete rows, but do you know of a way to get the row information to adjust to the new row
Thanks
Joa

----- Frank Kabel wrote: ----

H
sorry, my fault. Misread your question. Tr
=HLOOKUP($M$3,$Q$5:$DO$200,ROW(3:3),FALSE

and copy dow

-----Original Message----
Hi Frank
Your suggestion changes the lookup value (M3 becomes M4),

but the row index number (the second '3', the one
before 'false') remains the same. I need to keep the 'M3'
the same (hence the $M$3) but have the second '3' change
to the subsequent number

 

[Frank Kabel]

Hi
what rows do you delete and how do you want this formula to change?

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks, Frank. This worked to solve the problem I posted. Now,

though, if I delete a row in the spreadsheet, the formula does not
automatically adjust (it stays the same). I can manually re-copy the
formula every time I have to delete rows, but do you know of a way to
get the row information to adjust to the new row?

 

[Joan]

I have a spreadsheet with approx 50 rows of data. Each month, I need to delete the rows which have gone to zero. When I delete the row, the rows below move up, but the row index number remains the same. For example, assume that I have the following formulas in successive rows

Row 1 =HLOOKUP($M$3,$Q$5:$DO$200,ROW(3:3),FALSE
Row 2 =HLOOKUP($M$3,$Q$5:$DO$200,ROW(4:4),FALSE
Row 3 =HLOOKUP($M$3,$Q$5:$DO$200,ROW(5:5),FALSE
Row 4 =HLOOKUP($M$3,$Q$5:$DO$200,ROW(6:6),FALSE

Now assume that I delete Row 2. I would now want the formula in Row 3 to be changed to =HLOOKUP($M$3,$Q$5:$DO$200,ROW(4:4),FALSE) and the formula in Row 4 to be changed to =HLOOKUP($M$3,$Q$5:$DO$200,ROW(5:5),FALSE). I'm just trying to get it to work the way Excel normally works with a relative reference
Thanks
Joan
----- Frank Kabel wrote: ----

H
what rows do you delete and how do you want this formula to change

-
Regard
Frank Kabe
Frankfurt, German

Thanks, Frank. This worked to solve the problem I posted. Now

though, if I delete a row in the spreadsheet, the formula does no
automatically adjust (it stays the same). I can manually re-copy th
formula every time I have to delete rows, but do you know of a way t
get the row information to adjust to the new row