Return the last value in a list

Excel Tip #025

Return the last value in a list

You would have faced some situations where you want to get the last value in the list. And this list will not be static, the list will grow further and still the the last value has to be returned. Quite interesting...!

There might be multiple ways but as far I know, there are 2 ways in which we can get this done. But as far I consider, there are loopholes in all the 2 methods. Let us get in detail with all the 2 methods.

Method 1

This method uses the function LOOKUP. Let us see get to know about this function.

LOOKUP function returns a value from a range (one row or one column) or from an array. The syntax is:
LOOKUP( lookup_value, lookup_range, [result_range] )
(or)
LOOKUP( lookup_value, lookup_range )

We are going to use a hidden excel function in this method which is 9e9. So what does this 9e9 give? 9e9 is just a notation for 9,000,000,000 which is considered as the largest value in the array. It is nothing like an exponential function, 9 followed by 9 zeroes in short.

The formula is,
=LOOKUP(9e9,A:A)

Once you type this formula, 9e9 gets converted to 9,000,000,000. The formula will return the last value as the list grows.
Method 1 - LOOKUP & 9e9
Loophole - Method 1:

1) It is used only for values and doesn't consider character in the list
2) The values need not be continuous. If you enter a number in cell A20 leaving cells A11 to A19 as blank, it will return the value entered in A20. This is a loophole because, if accidentally entered a number at the end, and then sending the report as it is will screw up everything.

Method 2

This method uses the functions INDEX & COUNT/COUNTA.

INDEX - Returns a value in a table based on the intersection of a row and column position within that table
COUNT - Counts the numbers of cells in a range that contain numbers
COUNTA - Counts the numbers of cells in a range that are not empty (doesn't consider whether it is number or character)

The formula is,
=INDEX(A:A,COUNTA(A:A))
Method 2 - INDEX & COUNT
One main advantage in this method is if you use COUNTA function, then we can make use for character (or) for strings too.

Loophole - Method 2:

1) If you are using COUNT and if there are any blank values in between, the result will not be proper. The list should be continuous and the values should start from cell 1.

So which of these methods will you be using?

Comments

Post a Comment

Popular posts from this blog

VLOOKUP - Exact Match & Approximate Match

Image
Excel Tip #013 VLOOKUP - Exact Match & Approximate Match In the previous tip #012 (VLOOKUP & INDEX + MATCH), we learnt about VLOOKUP, it's syntax and how to use it. We also learnt about its drawback and how INDEX & MATCH can be helpful. Like said in the previous tip, there are 2 match option available in VLOOKUP - Exact & Approximate Match . Let us first refresh the syntax of VLOOKUP. Syntax: = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) In simple terms, =  VLOOKUP (value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match) Let's have a closer look at the last argument you supply to the Excel VLOOKUP function - range_lookup. You are likely to get different results in the same formula depending on whether you enter TRUE or FALSE (Approximate or Exact). If range_lookup is set to FALSE , the formula searches for exact match, i.e. for the lookup value exac
Read more

Row 1 Is Missing. How to unhide?

Image
Excel Tip #030 Row 1 Is Missing. How to unhide? As you know, hiding and unhiding rows in Excel is simple as selecting them, right-clicking and choosing Hide or Unhide . The trick to selecting hidden rows, is to select a row above or below and then drag to included the hidden rows. For example, if you accidentally hide row 1 of your worksheet or inherit someone else's worksheet with one or more of the top rows hidden, you may be wondering how to unhide them since apparently you cannot select them. Row 1 Hidden There is a really easy solution. 1) Point to the row 2 header , press the left mouse button and drag upward to the top of the worksheet. Although you cannot see it, row 2 and row 1 are now selected 2) Right click anywhere on row 2 3) Choose Unhide from the right-click menu Unhiding hidden Row 1 NOTE : This also works for multiple hidden rows. There is another method of doing the same if Row 1 is hidden. 1) Go to the top left corner of the sheet abov
Read more

Search Backwards in Excel

Excel Tip #022 Search Backwards in Excel Sometimes we take some things for granted in Excel and assume that everyone else knows too. Here's a tip which lot of Excel users don't know about it. Many Excel users know that the quickest way to find something in a large worksheet is to use Excel's Find command. On the Home tab, in the Editing group, click Find & Select , then Find or, preferrably, use the keyboard shortcut CTRL+F instead. Each time you click Find Next you will be taken to the next matching cell, starting from the active cell and going down the sheet until you finally come to the last one. But what if you want to find the 'last' matching value in a sheet and there are dozens or even hundreds of matching values? You don't want to click through each one until you finally reach the end. Here's a simple trick that will save you some time. Select a cell below the range you want to search ( CTRL+END is a shortcut you can use to quick
Read more