Finding the Latest Value in a Range

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.