How to return the last cell in a column that LOOKUP isn't working for

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

Merging cells always complicates worksheet formulas.

1. Use a dynamic named range for the driver check-in range:
name: DriverCheckIn
refers to:

Please Login or Register  to view this content.

To point TO the last row of the range, you will use the ROWS() function to return the current number of rows, so in
AG3:

Please Login or Register  to view this content.

Please try at
AG3
=LOOKUP(9^9,INDEX($A:$A,79*(ROWS(AG$2:AG2)-1)+42):INDEX($A:$A,79*(ROWS(AG$2:AG2)-1)+85))
AH3:AJ3
=VLOOKUP($AG3,INDEX($A:$A,79*(ROWS(AH$3:AH3)-1)+42):INDEX($E:$E,79*(ROWS(AH$3:AH3)-1)+85),COLUMNS($AF3:AH3),)

That works for AG3 and AH3 but AI3 and AJ3 just repeat AH3.

And what would I change in that formula to put the Driver Check-In in Test Route 2 in AG4:AJ4?

Thanks a lot, I think I'm close to what I need.

Originally Posted by Zypheris

That works for AG3 and AH3 but AI3 and AJ3 just repeat AH3.

=VLOOKUP($AG3,INDEX($A:$A,79*(ROWS(AH$3:AH3)-1)+42):INDEX($E:$E,79*(ROWS(AH$3:AH3)-1)+85),COLUMNS($AF3:AH3),)
Please check last term no $ in front of AH3

Originally Posted by Zypheris

And what would I change in that formula to put the Driver Check-In in Test Route 2 in AG4:AJ4?

Select AG3:AJ3 and drag the formula down.

If this not work, Please upload sheet with the formula that not work.

AG3:

Please Login or Register  to view this content.

Drag across and manually change the column numbers (1,3,4,5) because of the merged cells in A:F.

Try these formulas

In AG2 then drag down

Please Login or Register  to view this content.

In AH2 then drag across up to column AJ

Please Login or Register  to view this content.