How to Find Last Cell Value & Position in excel
Welcome to Excel Advisor :-
HOW TO FIND LAST CELL VALUE & IT'S POSITION.
LOOKUP
Yes !!! You heard it right.
LOOKUP Function has the ability to do this for you in a very easy way.
LOOKUP SYNATX - LOOKUP Bracket Open WHAT YOU HAVE TO SEARCH, COLUMN WHERE OUR SEARCH ENTITY LIES, COLUMN WHERE OUR RESULT LIES Bracket Close
1. Last Value in a ROW :-
Refer given example & USE LOOKUP Bracket Open 2,1/ Bracket Open B1:B7 **"" Bracket Close , Bracket Open B1:B7 Bracket Close Bracket Close
2 = We haven taken 2 as a default
B1:B7 **"" = It will check whether there is any space in between or not. In this example, we don't have any space so it will GIVE RESULTS LIKE Bracket Open TRUE,TRUE,TRUE,TRUE,TRUE,TRUE Bracket Close
1/B1:B7 **"" = 1/ Bracket Open TRUE,TRUE,TRUE,TRUE,TRUE,TRUE Bracket Close = 1/ Bracket Open 1,1,1,1,1,1 Bracket Close = 1,1,1,1,1,1
NOW IT LOOKS LIKE THIS
LOOKUP Bracket Open 2, Bracket Open 1,1,1,1,1,1 Bracket Close , Bracket Open B1:B7 Bracket Close
So now the FIRST ARGUMENT "2" comes into role. It will search for "2" under Bracket Open 1,1,1,1,1,1 Bracket Close . If not found it will look for 1 smaller number i.e. 2-1 = 1 from the last which means last non-empty entry from the last.
NOW IT LOOKS LIKE
LOOKUP Bracket Open LAST ENTRY FROM THE BOTTOM, Bracket Open B1:B7 Bracket Close
It will gives us the LAST CELL VALUE in a COLUMN
1. LAST CELL POSITION IN A COLUMN :-
Refer given example & USE LOOKUP Bracket Open 2,1/ Bracket Open B1:B7 **"" Bracket Close ,ROW Bracket Open B1:B7 Bracket Close Bracket Close
2 = We haven taken 2 as a default
B1:B7 **"" = It will check whether there is any space in between or not. In this example, we don't have any space so it will GIVE RESULTS LIKE Bracket Open TRUE,TRUE,TRUE,TRUE,TRUE,TRUE Bracket Close
1/B1:B7 **"" = 1/ Bracket Open TRUE,TRUE,TRUE,TRUE,TRUE,TRUE Bracket Close = 1/ Bracket Open 1,1,1,1,1,1 Bracket Close = 1,1,1,1,1,1
NOW IT LOOKS LIKE THIS
LOOKUP Bracket Open 2, Bracket Open 1,1,1,1,1,1 Bracket Close , Bracket Open B1:B7 Bracket Close
So now the FIRST ARGUMENT "2" comes into role. It will search for "2" under Bracket Open 1,1,1,1,1,1 Bracket Close . If not found it will look for 1 smaller number i.e. 2-1 = 1 from the last which means last non-empty entry from the last.
NOW IT LOOKS LIKE
LOOKUP Bracket Open LAST ENTRY FROM THE BOTTOM,ROW Bracket Open B1:B7 Bracket Close
ROW Bracket Open B1:B7 Bracket Close will gives us 1,2,3,4,5,6,7 i.e. the CELL POSITION
NOW THE LAST STEP WILL WORK AUTOMATICALLY LIKE LOOKUP Bracket Open LAST ENTRY FROM THE BOTTOM, Bracket Open 1,2,3,4,5,6,7 Bracket Close = 7 i.e. LAST NON EMPTY CELL POSITION
Checkout this video & If need any help contact undersigned
Reach me :-
[email protected] Bracket Open ~A learner Bracket Close
