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.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
There are times when it can be beneficial to combine both numbers and text in the same cell. This can be easily done ...
Discover MoreDo you want Excel to take the case of your text into account when it does comparisons in a formula? The IF statement ...
Discover MoreDo you need to generate strings of random characters? The ideas presented in this tip will help you do it in a hurry.
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