Copying HLookup Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi
use
=HLOOKUP($M3,$Q$5:$DO$200,3,FALSE)


--
Regards
Frank Kabel
Frankfurt, Germany

Joan said:
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
Click to expand...
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.
 
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

Joan said:
Hi
Click to expand...
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
 
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),
Click to expand...
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, 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),
Click to expand...
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
 
Hi
what rows do you delete and how do you want this formula to change?

--
Regards
Frank Kabel
Frankfurt, Germany

Joan said:
Thanks, Frank. This worked to solve the problem I posted. Now,
Click to expand...
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?
 
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

Joan said:
Thanks, Frank. This worked to solve the problem I posted. Now
Click to expand...
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
 
Back
Top