• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to find last entry in a column

F

flywhiz

New Member
I would like to create a formula in one cell that would look at a defined column area in the worksheet (only one column with multiple rows) and display the last entry on the row within that column regardless of whether the number is greater or less than the other numbers in that column.
Thanks for any help you can provide.
Glen
 
Last edited by a moderator:
Hi Glen ,

Use the following :

=INDEX(ListofNumbersWithBlanksInBetween,MATCH(99,ListofNumbersWithBlanksInBetween))

The named range ListofNumbersWithBlanksInBetween can be any column range reference e.g. J17:J53.

The 99 is just an arbitrarily large value , which should be larger than any number within the data range.

Note that in the MATCH function , the third parameter is omitted.

Narayan
 
NARAYANK991 said:
Hi Glen ,

Use the following :

=INDEX(ListofNumbersWithBlanksInBetween,MATCH(99,ListofNumbersWithBlanksInBetween))

The named range ListofNumbersWithBlanksInBetween can be any column range reference e.g. J17:J53.

The 99 is just an arbitrarily large value , which should be larger than any number within the data range.

Note that in the MATCH function , the third parameter is omitted.

Narayan
Click to expand...
Mr Narayan,
I have tried your answer i have getting #n/a. Kindly advice on this.

Thanks in advance
 
Hi. Thanks to everyone who responded! I ended up using the formula Somendra provided as it seemed to be easiest to apply. It works great. Thanks again!
Glen
 
@Syedali

LOOKUP function require a look up value which is looked against a lookup array which need to be sorted in ascending order and it deliver the result from the result array.

But if you check a large number against a array which is not sorted, than it deliver the last element in that array.

Than it is also note worth that LOOKUP function ignores error if any there are in lookup array. So the lookup array in my formula will give like {1;1;Div/0;1;1;Div/0}. Div/0 error for blank cells. So these will get ignored and Since 2 is bigger than 1 so it will give the last cell with data in it.

Hope this makes some sense, try evaluating the function.

Regards,
 
@NARAYANK991 Sir,

I think you should use Some big number in place of 99 in MATCH function. Because if there is some number bigger than 99 in the range than it will not give exact result.

Regards,
 
Somendra Misra said:
@Syedali

LOOKUP function require a look up value which is looked against a lookup array which need to be sorted in ascending order and it deliver the result from the result array.

But if you check a large number against a array which is not sorted, than it deliver the last element in that array.

Than it is also note worth that LOOKUP function ignores error if any there are in lookup array. So the lookup array in my formula will give like {1;1;Div/0;1;1;Div/0}. Div/0 error for blank cells. So these will get ignored and Since 2 is bigger than 1 so it will give the last cell with data in it.

Hope this makes some sense, try evaluating the function.

Regards,
Click to expand...
Thanks Mr Misra. am still try to understand
 
Hi Misra ,

I think I have explained it in my post ; I don't think anyone should just copy a formula without going through the post or trying to understand the formula.

Narayan
 
Syedali said:
Hi sir,
In between any blank is their means it is not working
Click to expand...

He did you say there were blanks in the row... or I would have structured this differently...

Looks like there are a few good options here :)

Respectfully,
PaulF
 
You must log in or register to reply here.
Back
Top