Written by Allen Wyatt (last updated May 2, 2026)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
Nelson has a worksheet where column A contains dates in ascending order. Columns B:G contain numeric values derived from a study his company is conducting. In cell J2 Nelson has a value that may appear somewhere in columns B:G. He needs a formula that will return the latest date for which the J2 value may appear in B:G.
There are several ways that you can approach this task, and the biggest determining factor of which you should use depends on two things—what version of Excel you are using and what you want the formula to return if the value in J2 is not found within the study results.
For our purposes, let's assume that Nelson has data that is in the range A2:G113, with dates being in the first column. If you are using Excel 2024 or Microsoft 365, then there are two formulas you could use:
=MAX(FILTER(A2:A113,BYROW(B2:G113=J2,OR),0)) =LET(d,MAX(A2:A113*BYROW(B2:G113=J2,OR)),IF(d,d,"Not Found"))
Either formula returns a value you will need to format as a date. If the value in J2 isn't found in B2:G113, then the first formula returns a 0 (which will end up looking like 1/0/00) and the other returns the text "Not Found."
If you want the first formula to, instead, return "Not Found," then you can adjust it slightly so that FILTER doesn't return a 0 and wrap it in the IFERROR function:
=IFERROR(MAX(FILTER(A2:A113,BYROW(B2:G113=J2,OR))),"Not Found")
If you are using an older version of Excel, then you can use either of these formulas:
=LOOKUP(2,1/(MMULT(--(B:G=J2),TRANSPOSE(COLUMN(B:G)))>0),A:A) =MAX(IF(B2:G113=J2,A2:A113))
If you are using Excel 2019 or earlier, you need to use Ctrl+Shift+Enter to make sure these are evaluated as array formulas. The first formula evaluates the entirety of columns B:G, while the second just looks at B2:G113. If the value in J2 isn't found in the range, then the first formula returns an #N/A error, but the second returns a 0. They could be wrapped in an IFERROR function to handle this situation, as well.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13980) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
If you have a series of numbers separated by commas in a single cell, Excel treats the series as text. If you want to ...
Discover MoreSometimes it can be helpful to show both a numeric value and a percentage in the same cell. This can be done through ...
Discover MoreYou can easily sum a series of values in Excel, but it is not so easy to sum the absolute values of each value in a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2026-05-02 15:32:20
J. Woolley
Re. its last two formulas, the Tip says, "If the value in J2 isn't found in the range, then the first formula returns an #N/A error, but the second returns a 0. They could be wrapped in an IFERROR function to handle this situation, as well."
Since 0 is not an error, here are several ways for the second formula to handle this situation when the formula's cell is formatted as Date like mm/dd/yy:
=MAX(IF(B2:G113=J2, A2:A113))
If J2 isn't found, 0 is displayed as 01/00/00.
=MAX(IF(B2:G113=J2, A2:A113, -1))
If J2 isn't found, -1 is displayed as ###### (filling the cell).
=LET(x, MAX(IF(B2:G113=J2, A2:A113)), IF(x>0, x, "Not Found"))
If J2 isn't found, 0 is displayed as Not Found. LET requires Excel 2021.
=TEXT(MAX(IF(B2:G113=J2, A2:A113)), "mm/dd/yy;;""Not Found""")
If J2 isn't found, 0 is displayed as Not Found; otherwise, the result is text (the cell's Number format is ignored).
=IFERROR(DATEVALUE(TEXT(MAX(IF(B2:G113=J2, A2:A113)), "mm/dd/yy")), "Not Found")
If J2 isn't found, #VALUE! is displayed as Not Found; otherwise, the result is a date serial number (the cell's Number format is applied).
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2026 Sharon Parq Associates, Inc.
Comments