References in LOOKUP formulas - automatic row/column update?

[jenniferohearn]

Hello - when I create a LOOKUP formula, is there a way to get the row or columns that are being searched to update?

For example, I am going to create a HLOOKUP formula to populate an address. Let's say I have 2 options of addresses stored in columns K&N. My source data is stored as a standard address Row 1 Name, Row 2 Street, Row 3 City, State, Zip.

Say in cell A1, I write hLOOKUP($J$1,$K$1:$N$3,1,FALSE). It will give me the Row 1 Name. Great. But when I copy that formula down, it keeps searching for the result in row 1, and I have to manually update the formula to tell it to search Row 2 for Street, and again in Row 3 for City.

It's not a huge deal until I'm in a sheet that I'm developing now and I have 50+ rows of lookups that populate based on a user's choice of data in J1. I have the source data organized in the right order, so it would be nice if Excel would assume I want the next row when I'm copying the formula down. Just wondered if there was a quicker way to get around the manual update. Thanks a lot!

 

[Aladin Akyurek]

X2, copied down:

=HLOOKUP($J$1,$K$1:$N$3,ROWS($X$2:X2),0)