In this article, we will learn how to LOOKUP date with last entry in a row or column in Excel.
Scenario:
We have a long table of entries of products with blank cells between the values. We need to find the last entry of the product ordered and date associated with it.
How to solve the problem?
We will be using the LOOKUP function. Let's first get some knowledge about the function. LOOKUP function finds the estimated value in the range and returns the value from the range or its corresponding value.
Syntax:
lookup_value: value to look up for.
lookup_vector: array where the function looks for the lookup_value.
[result vector]:[optional] if the return array is different from the lookup array.
But here we will be using one of the attributes of the LOOKUP function i.e. if LOOKUP function can't find an exact match, it will match with just the previous value when it stops. For this, we will be using the below syntax.
Generic Formula:
list : lookup list
date_range : date value to be returned from the range
Example:
Here we have products delivered with their date and amount and we need to find the last delivered product date for each product.
So here we have some blank cells between the date value entries.
Formula:
C$2:J$2 : fixed date_range
Explanation:
Press Enter to get the results.
As you can see, 10-Apr is the last entry date for the Wheat product.
Now copy the formula to other cells using the shortcut Ctrl + D or drag down cell option from right bottom of the cell.
As you can see, the function returns the value as required. You can customize Excel functions as required.
Here we have observational notes for using the formula.
Notes:
Hope you understand how to LOOKUP date with last value in list in Excel. Explore more articles on lookup value here. Please feel free to state your queries below in the comment box. We will certainly help you.
Related Articles
Use INDEX and MATCH to Lookup Value : INDEX & MATCH function to look up value as required.
SUM range with INDEX in Excel : Use INDEX function to find the SUM of the values as required.
How to use LOOKUP function in Excel : Find the lookup value in the array using the LOOKUP function explained with example.
How to use the HLOOKUP function in Excel : Find the lookup value in the array using the HLOOKUP function explained with example.
Popular Articles:
50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
I have a complicated spreadsheet with lots of formulae for a weight loss/control project. As it's a tracking spreadsheets, dates with corresponding data gets added, and over time the position in the spreadsheet where certain data needs to be entered alters. Because it's a large spreadsheet, I needed to use navigation macros. However because the macros use fixed references, although they will be correct at the time of inception over time they will become displaced from where they need to be taking you because of the extension of the data in the column. As the spreadsheet is intended for non-power users who wouldn't know how to alter the macros, I'm looking for a method to dynamically alter the macro so that it goes to a dynamically altered location determined by today's date. So to explain further, there's a column with a list of dates starting in the past, and continuing with daily additions to the present. There's a cell at the top with a dynamically updating date. I would like to write a macro that instead of taking you to the same date cell in the list, always takes you to the last cell in the list. Any ideas?