Written by Allen Wyatt (last updated December 24, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
In column A Ron has a series of numeric values sorted in ascending order. (The values are the results of formulas.) He would like to use a different formula to return the address of the first cell in column A in which the value exceeds a threshold that is found in cell E3. The problem is, Ron has no idea on how to put together such a formula.
There are multiple formulaic approaches you can take to this issue, and the one you choose can depend on the nature of the data you are analyzing. If you want the address of the cell in which the threshold is first met or first exceeded, then these formulas will do fine:
If, however, you want the address of the first cell in which the threshold is first truly exceeded (in other words, matches with the threshold are not counted), then the following formulas will work:
=ADDRESS(MATCH(E3,A:A,1)+1,1) =ADDRESS(ROW(XLOOKUP(E3+0.001,A:A,A:A,,1)),1) =ADDRESS(XMATCH(E3+0.001,A:A,1),1) =ADDRESS(MATCH(E3,A:A,1)+1,COLUMN(A:A)) ="A" & MATCH(E3,A:A,1)+1 =CELL("address",INDEX($A:$A,MATCH(E3,$A:$A,1)+1))
If nothing in column A exceeds the threshold in E3, then none of the above formulas work—they simply return the address of the cell in column A that is one row beyond the last value. That is similar to what happens with the following formulas:
=CELL("address",INDEX(A:A,MATCH(TRUE,INDEX(A:A>E3,0),))) =ADDRESS(MATCH(MIN(IF(A:A>E3,A:A)),A:A,0),1) =ADDRESS(MATCH(1,--(A:A>E3),0),COLUMN(A:A),4) =SUBSTITUTE(CELL("address",INDEX(A:A,MATCH(1,--(A:A>E3),0))),"$","")
The difference with these formulas is that they don't return an erroneous address when the threshold is not exceeded by any value, but instead returns an #N/A error. This means that any of them could be wrapped within an IFNA function that "catches" the error condition and allows you to indicate what should be done. Here's an example of one of the formulas thusly wrapped:
=IFNA(ADDRESS(MATCH(MIN(IF(A:A>E3,A:A)),A:A,0),1),"Threshold not Exceeded")
This wrapping obviously makes the formulas a bit longer, but it makes the results of the formula more understandable.
Finally, it should be noted that all of these formulas should work just fine in Excel 2019 or later versions. In earlier versions, most of the formulas need to be entered as array formulas using Ctrl+Shift+Enter. (You should be able to look at the results returned by the formula and tell if you should enter it as an array formula.)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12999) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
If you have a lot of values in a single row, you might want to pull the last non-zero value from that row. There are a ...Discover More
Trying to calculate how much people owe you? If you charge interest or service charges on past-due accounts, there are a ...Discover More
If you need to switch between viewing formulas and viewing the results of those formulas, you'll love the keyboard ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.