Automatically update LOOKUP formula when inserting additional row

%3CLINGO-SUB%20id%3D%22lingo-sub-2621079%22%20slang%3D%22en-US%22%3EAutomatically%20update%20LOOKUP%20formula%20when%20inserting%20additional%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2621079%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20want%20to%20find%20the%20last%20number%20in%20a%20column%20starting%20from%20a%20specific%20cell.%20However%2C%20when%20I%20add%20a%20new%20row%2C%20I%20need%20to%20update%20the%20formula%20manually%20to%20include%20the%20new%20row.%20Is%20there%20a%20way%20to%20have%20the%20formula%20below%20automatically%20update%20when%20inserting%20an%20additional%20row%3F%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3DLOOKUP(2%2C1%2F(ISNUMBER(H50%3AH56))%2CH50%3AH56)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ECurrently%2C%20I%20am%20updating%20the%20formula%20manually%20to%20this.%20For%20this%20example%2C%20I%20would%20update%20H56%20to%20H57%3A%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3DLOOKUP(2%2C1%2F(ISNUMBER(H50%3AH57))%2CH50%3AH57)%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-08-06%20at%2012.56.09%20PM.png%22%20style%3D%22width%3A%20720px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301113iCFCC8DD14D9EB307%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-08-06%20at%2012.56.09%20PM.png%22%20alt%3D%22Screen%20Shot%202021-08-06%20at%2012.56.09%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2621079%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2621123%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20update%20LOOKUP%20formula%20when%20inserting%20additional%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2621123%22%20slang%3D%22en-US%22%3EThis%20is%20one%20of%20the%20great%20features%20that%20converting%20a%20range%20to%20a%20table%20brings%20you%3A%20never%20update%20a%20cell%20reference%20again%20when%20data%20is%20added%3A%20%3CA%20href%3D%22https%3A%2F%2Fjkp-ads.com%2Farticles%2Fexcel2007tables.asp%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fjkp-ads.com%2Farticles%2Fexcel2007tables.asp%3C%2FA%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I want to find the last number in a column starting from a specific cell. However, when I add a new row, I need to update the formula manually to include the new row. Is there a way to have the formula below automatically update when inserting an additional row?

=LOOKUP(2,1/(ISNUMBER(H50:H56)),H50:H56)

 

Currently, I am updating the formula manually to this. For this example, I would update H56 to H57:

=LOOKUP(2,1/(ISNUMBER(H50:H57)),H50:H57)

 

Screen Shot 2021-08-06 at 12.56.09 PM.png

1 Reply
This is one of the great features that converting a range to a table brings you: never update a cell reference again when data is added: https://jkp-ads.com/articles/excel2007tables.asp