How to Find the Last Row with a Specific Value in Excel (6 Methods)

Formula Breakdown

• IF($B$5:$B$15=H5,ROW($B$5:$B$15)) yields the result as {5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;15}. Where 5 and 15 indicate the Row number for Catherine.
• The MAX Function will take 15 as the maximum number and give us the result.
• Explanation: This array formula checks each cell in column A for “Value“. If true, it multiplies the row number by 1, otherwise by 0. MAX returns the highest row number where “Value” is found.
• Benefits: This method is simple and straightforward. You can use it for small to medium datasets where array formulas are manageable.

Formula Breakdown

• ROW(B5:F15)*(B5:B15=H5) gives us the output as {5;0;0;0;0;0;0;0;0;0;15}
• SUMPRODUCT(MAX{5;0;0;0;0;0;0;0;0;0;15}) will give the final output as 15.
• Explanation: It is similar to the MAX function but uses SUMPRODUCT to handle the array, returning the maximum row number where “Value” is found.
• Benefits: You can avoid array formulas, which can be complex. You can use this formula if you prefer non-array formulas for better readability and maintenance.

Here, the function SUMPRODUCT(MAX(ROW($B$5:$B$15)*(H5=$B$5:$B$15))-4 gives us the result 11. 11 indicates the Row number in the table. As you can see, the last row in the worksheet is 15, but the data in our table started from Row 5, so we use -4 in the formula.
Then, the formula =INDEX($F$5:$F$15,11) will return a Very High output.

Explanation: It works by identifying the row numbers in B5 where the value matches H5, calculating the maximum row number from these matches, adjusting it for the starting row, and then using INDEX to return the corresponding value from F5.
Benefits: This method is dynamic, avoids array formulas, and is versatile for finding the last occurrence of a value in one column and retrieving a related value from another column.

• LOOKUP searches for the last occurrence of “Value” in column A and returns the corresponding row number. The LOOKUP function then searches for the value 2 (which will never be found) but, due to its lookup behavior, returns the last numerical match (i.e., the last 1) and uses that position to fetch the corresponding value from F5.
• Explanation: This formula finds the last occurrence of a specific value (H5) in the range B5 and returns the corresponding value from F5. It does this by creating an array where each element is 1 if the corresponding element in B5 equals H5 and #DIV/0! otherwise.
• Benefits: This method is efficient and easy to implement for locating the last match in a dataset.

• Explanation: This formula finds the last row with a specific value (H5) in the range B5 and returns the corresponding value from F5. It uses MATCH to locate the last occurrence of H5 by creating an array of 1s and #DIV/0! errors, where 1 corresponds to matches. MATCH searches for 2, which ensures it returns the position of the last 1. INDEX then uses this position to fetch the value from F5.
• Benefits: Compatible with dynamic arrays. You can use it for dynamic tables and newer Excel versions.

• Explanation: This user-defined VBA function loops through each cell in the specified range and checks if it matches the value. It updates the variable to the current row number when a match is found.
  • Finds the last occurrence of SpecificValue in SpecificRange and returns the value from the specified column (LastColumnNumber).
  • Iterates from the last row to the first row in SpecificRange.
  • Check if the cell in the first column matches SpecificValue.
  • Returns the value from the LastColumnNumber of the matching row and exits the function.
• Benefits: It’s an automated solution. Useful for repetitive tasks or when you want to create a reusable function.