ExcelHub - Learn Excel Easily

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...

Excel Tip #012 VLOOKUP & INDEX + MATCH Excel has many formulas which functions differently. To analyze data, one of the most important formula is VLOOKUP and INDEX + MATCH . Let us now get into the details of it. VLOOKUP : Let us consider the below data as an example. Sales Data Let us say, we want to know how much sales did Jerald make?  VLOOKUP  can be used to answer this question. What does VLOOKUP actually do?      VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns. 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) The final parameter is optional but very important. In the next tutorial, I will provide a few examples explaining how to correctly make formulas for exact (or) ...