How to use ROW() in an Excel VLOOKUP formula?

I have two formulas for a cell reference: ="D"&ROW() and ="D$"&(ROW()-1)

These work fine on their own, but not when I put them in a VLOOKUP:=VLOOKUP("D"&ROW(),D$3:"D$"&(ROW()-1),1,0)

Using the ADDRESS function gave the same error as using the ROW function. I have successfully worked around this using INDIRECT in the VLOOKUP formula, but that seems clunky and unnecessarily complicated. Is there a way to do this without using INDIRECT?

1

Best Answer

INDIRECT is Volatile; use INDEX instead:

=VLOOKUP(INDEX(D:D,ROW()),D$3:INDEX(D:D,ROW()-1),1,FALSE)

But upon typing that I see that it would be easier to just put the first row in the reference and keep it dynamic. So if the first row where 4 then

=VLOOKUP(D4,D$3:D3,1,FALSE)

Then as it is dragged/copied down only the D4 and the second D3 would change, leaving the first anchored on D3.

Random Posts

(WSL error at Startup) Press any key to continueTurning a Matrix into a VectorShould I use UIImage or CGImage in a Swift iOS App?How do I detect collision in pygame?Is there a way to add test notes automatically to Testflight internal testing with XcodeCloudHow to set a string's color [duplicate]Apache Kafka and message delivery assuranceESLint Parsing error: Unexpected tokenHow to fix an "Argument passed to call that takes no arguments" error?logistic regression get the sm.Logit values (python, statsmodels)Is best first search optimal and complete?Changing the Fork() system callPower Automate Flow: Rename a file's attachment with the mail receive time and storing the attchmenterror code 1292 incorrect date value mysqlreact-router getting this.props.location in child componentsInstall ipdb for Python 3? [duplicate]How can i link GLFW and Dear ImGUI in CMAKEKeycloak in Docker with proxy such as nginx using non-standard portsAssigning Image from URL with DragonflyControl AWS Sagemaker costs