How to find last position of a value in range in Excel

Thanks to Vijay Rawat (one of my student) who asked this question to me and inspired me to find the solution of the same.

Sometimes we face a situation in excel, when we want to find the last position of a value in a

range(Vertical/Horizontal any)

Suppose we have data in a range from A1 to A10 like below:

 

9-25-2016-12-08-26-pm

 

In this data we can see that Anant is repeating many times, Now the problem is how to find the last position of Anant in this range (i.e. last position is 9th, by looking manually)

 

Which formula should we apply in Cell B1 to find this….

 

Here is the answer of the same.

 

Select cell B1 and write below mentioned formula:

=max(row(1:10)*(A1:A10=”Anant”))

It will give you answer 9 because 9th is the last position of “Anant” in the given range

 




Lets have a look on how this formula works:

 

Row(1:10) :- gives number from 1 to 10 like shown below (because our data is from row 1 to 10 so we mentioned Row(1:10) 

 

{1;2;3;4;5;6;7;8;9;10}

 

A1:A10=”Anant” :- it will give true or false wherever “Anant” is written it will return TRUE (i.e. 1), else false (i.e.0) like this:

 

{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

 

 

Now * will multiply row numbers to 1 or 0 like shown below:

 

{1;0;0;4;0;0;0;0;9;0}

 

And Finally max will give us the maximum value generated by the above formula which is 9

 

Be an Expert in MS Excel… Click here to Enroll for complete course

If you like this trick, then please share it using links below…..

Happy Learning

 

Please follow and like us:
RSS
Follow by Email
Facebook
Google+
http://klick2learnmore.com/how-to-find-last-position-of-a-value-in-range-in-excel/
Twitter
YouTube
YouTube
LinkedIn

2 responses to “How to find last position of a value in range in Excel”

  1. Shermaine says:

    I love your blog.. very nice colors & theme. Did you create this website yourself or did you hire someone to do it for you? Plz answer back as I&2#718;m looking to design my own blog and would like to know where u got this from. thanks a lot

Leave a Reply

Your email address will not be published. Required fields are marked *