'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Find the Oldest Order Date and Return Its Order Number in Excel
Question details
The user needs a formula to look up and return the specific order number associated with the oldest order date for a given item, bypassing VLOOKUP's default behavior of returning the first match.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Performing data analysis or inventory management where multiple orders exist for the same item across different dates.
- Observed behavior
- Standard VLOOKUP functions only return the first matched item in the list, failing to identify and return the record with the minimum (oldest) date.
Ensure that your date column is formatted as actual dates rather than text strings, and that all data ranges in your formula are of equal length.
Use INDEX, MATCH, MIN, and IF (Array Formula)
Combine these functions into an array formula to isolate the minimum date for a specific item and return the corresponding order number.
Since VLOOKUP stops at the first match it encounters, you must use an array formula with MIN and IF to evaluate all dates associated with your specific item. Once the oldest date is found, MATCH and INDEX work together to retrieve the corresponding order number.
Click on the blank cell where you want the final order number to appear.
Type the formula: =INDEX(C2:C4,MATCH(MIN(IF(A2:A4="1001 SPARE PART",B2:B4)),B2:B4,0)). Adjust the ranges so that A contains the items, B contains the dates, and C contains the order numbers.
Press Ctrl + Shift + Enter to evaluate the formula as an array. If you are using Excel 365 or Excel 2021, you can simply press Enter.
Use Dynamic Array Functions (TAKE, DROP, SORT, FILTER)
Utilize modern Excel dynamic array functions to filter the dataset by the item, sort it by date, and extract the first (oldest) result.
Sort the Data First, Then Use VLOOKUP
A simpler, non-formula-heavy approach where you manually sort the dataset by date before running a standard VLOOKUP.
Extract Complex Data Matches Easily with WPS Office
WPS Spreadsheet provides seamless support for advanced array formulas and modern lookup functions, allowing you to quickly extract precise data like the oldest order dates without compatibility issues.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the item, date, and order data.
- 2. Select the destination cell: Click the empty cell where you want to display the oldest order number.
- 3. Enter the lookup formula: Type the formula: =INDEX(C2:C100,MATCH(MIN(IF(A2:A100="Target Item",B2:B100)),B2:B100,0))
- 4. Execute the formula: Press Ctrl + Shift + Enter to process the array and instantly get the correct order number.
Frequently Asked Questions
Why does VLOOKUP return the wrong date when there are duplicates?
VLOOKUP searches your data from top to bottom and stops at the very first exact match it finds. If your dataset isn't sorted by date, the first match it hits might not be the oldest one, which is why an array formula or pre-sorting is required.
Do I always need to press Ctrl + Shift + Enter for MIN and IF?
In older versions of Excel, you must press Ctrl + Shift + Enter to evaluate the formula as an array. In Microsoft 365 or newer versions of WPS Office, the modern dynamic array engine allows you to simply press Enter.
Can I use XLOOKUP to find the oldest date?
Yes, but only if your data is already sorted by date in ascending order, as XLOOKUP will return the first match by default. Alternatively, XLOOKUP can be nested with MIN and IF just like the INDEX and MATCH combination.
What happens if there are multiple orders on the exact same oldest date?
If there is a tie for the oldest date, the INDEX and MATCH formula combination will return the order number of the first occurrence of that specific minimum date reading from the top of your dataset.
