Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Use Data Form to input, edit and delete records in Excel
  • How to Create Column Chart in Excel
  • How to create Checklist in Excel
  • Subtotal function in Excel
  • Example of COUNTIFS with variable table column in Excel

References

  • How to use Excel INDIRECT Function
  • Count unique text values with criteria
  • Offset in Excel
  • Two-column Lookup in Excel
  • Get nth match with INDEX / MATCH in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation allow weekday only
  • Excel Data validation exists in list
  • Excel Data validation only dates between
  • Prevent invalid data entering in specific cells

Get last match in Excel

by

This tutorial shows how to Get last match in Excel using the example below;

Formula

{=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1))}

Explanation

To get the position of the last match (i.e. last occurrence) of a lookup value, you can use an array formula based on the IF, ROW, INDEX, MATCH, and MAX functions. In the example shown, the formula in H6 is:

{=MAX(IF(names=H5,ROW(names)-ROW(INDEX(names,1,1))+1))}

Where “names” is the named range C4:C11.

Note: this is an array formula and must be entered with control + shift + enter.

How this formula works

The gist of this formula is that we build a list of row numbers for a given range, matching on a value, and then use the MAX function to get the largest row number, which corresponds to the last matching value.

Working from the inside out, this part of the formula will generate a relative set of row numbers:

ROW(names)-MIN(ROW(names))+1

We are using the named range “names” for convenience. The result of the above expression is an array of numbers like this:

{1;2;3;4;5;6;7;8}

Notice we get 8 numbers, corresponding to the 8 rows in the table. See this page for details on how this part of the formula works.

For the purpose of this formula, we only want row numbers for matching values, so we use the IF function to filter the values like so:

IF(names=H5,ROW(names)-MIN(ROW(names))+1)

This results in an array that looks like this:

{1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE}

Note this array still contains eight items. However, only row numbers where the value in the named range “names” is equal to “amy” have survived (i.e. 1, 4, 7). All other items in the array are FALSE, since they failed the logical test in the IF function.

Finally, the IF function delivers this array to the MAX function, which returns the highest value in the array, the number 7, which corresponds to the last row number where the name is “amy”. Once we know the last matching row number, we can use INDEX to retrieve a value at that position.

Second to last, etc.

To get the second to last position, third to last, etc. you can switch from MIN function to the LARGE function like this:

{=LARGE(IF(criteria,ROW(range)-MIN(ROW(range))+1),k)}

where k represents “nth largest”. For example, to get the second to last match in the example above, you can use:

{=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2)}

As before, this is an array formula and must be entered with control + shift + enter.

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel
  • Not Equal To ‘<>‘ operator in Excel

Date Time

  • Get month from date in Excel
  • Convert decimal seconds to Excel time
  • Display Date is same month in Excel
  • Add workdays no weekends in Excel
  • How to get Holiday Date from Year in Excel

Grouping

  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers at uneven intervals in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign people to groups in Excel

General

  • Mark Workbook as Final in Excel
  • How to get original price from percentage discount in Excel
  • How to calculate percent sold in Excel
  • How to generate random number between two numbers in Excel
  • Spell Check in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning