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
Suppose we have data in a range from A1 to A10 like below:
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:
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)
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:
Now * will multiply row numbers to 1 or 0 like shown below:
And Finally max will give us the maximum value generated by the above formula which is 9
If you like this trick, then please share it using links below…..