SOLVED

Return last number in a column

%3CLINGO-SUB%20id%3D%22lingo-sub-2105021%22%20slang%3D%22en-US%22%3EReturn%20last%20number%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2105021%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%26nbsp%3B%20am%20pretty%20new%20to%20excel%20and%20i%20got%20a%20problem%20i%20cant%20seem%20to%20figure%20out%20no%20matter%20how%20many%20posts%20about%20it%20i%20have%20read.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUltimately%20i%20want%20to%20make%20excel%20show%20me%20the%20last%20number%20in%20a%20column%20that%20is%20not%200.%3C%2FP%3E%3CP%3EBut%20before%20i%20go%20there%20i%20wanted%20it%20to%20show%20me%20the%20last%20number%20in%20the%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20no%20matter%20what%20i%20try%20and%20do.%20And%20how%20many%20guides%20and%20posts%20i%20read%2Fwatch.%20I%20get%20the%20same%20error%20all%20the%20time.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel%20last%20number.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F250196i3D533DDEB17FD5AE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22excel%20last%20number.JPG%22%20alt%3D%22excel%20last%20number.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20is%20a%20screenshot%20of%20the%20error%20i%20always%20get%20no%20matter%20what%20formula%20i%20use%20LOOKUP%2FOFFSET%2FINDEX%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20can%20guide%20me%20in%20the%20right%20direction%20i%20would%20appreciate%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3EMarc%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2105021%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2105129%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20last%20number%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2105129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F945675%22%20target%3D%22_blank%22%3E%40Belthax%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CP%3E%3DLOOKUP(9.99999999999999E%2B307%2CG%3AG)%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20a%20finite%20range%20instead%20of%20the%20entire%20column%2C%20of%20course.%3C%2FP%3E%0A%3CP%3EThe%20last%20non-zero%20number%20is%3C%2FP%3E%0A%3CP%3E%3DLOOKUP(2%2C1%2F(G%3AG%26lt%3B%26gt%3B0)%2CG%3AG)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello All

 

I  am pretty new to excel and i got a problem i cant seem to figure out no matter how many posts about it i have read. 

 

Ultimately i want to make excel show me the last number in a column that is not 0.

But before i go there i wanted it to show me the last number in the column.

 

But no matter what i try and do. And how many guides and posts i read/watch. I get the same error all the time. 

excel last number.JPG

This is a screenshot of the error i always get no matter what formula i use LOOKUP/OFFSET/INDEX

 

If anyone can guide me in the right direction i would appreciate it.

 

Thanks in advance

Marc

4 Replies

@Belthax 

Use

=LOOKUP(9.99999999999999E+307,G:G)

You can use a finite range instead of the entire column, of course.

The last non-zero number is

=LOOKUP(2,1/(G:G<>0),G:G)

@Hans Vogelaar 

Thanks for the quick reply.

 

What im experiencing seems to be something else. Even when i past those formulas they produce the same error as on the picture.

Best Response confirmed by Belthax (New Contributor)
Solution

@Belthax 

Do you use comma as decimal separator (e.g. three-and-a-half is 3,5)?

If so, you should use semi-colons instead of commas in the formulas:

 

=LOOKUP(9,99999999999999E+307;G9:G28)

 

and

 

=LOOKUP(2;1/(G:G<>0);G:G)

@Hans Vogelaar 

 

That was the problem. Now its working perfectly.

 

Thanks alot for the assistance.