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

Data Analysis

  • Conditional Formatting New Rule with Formulas in Excel
  • Understanding Anova in Excel
  • How to create Gauge Chart in Excel
  • How to create a Histogram in Excel
  • How To Remove Duplicates In Excel Column Or Row?

References

  • How to use Excel VLOOKUP Function
  • How to use Excel MMULT Function
  • Convert text string to valid reference in Excel using Indirect function
  • Approximate match with multiple criteria in Excel
  • How to get first column number in range in Excel

Data Validations

  • Excel Data validation number multiple 100
  • Excel Data validation date in next 30 days
  • Excel Data validation whole percentage only
  • Excel Data validation date in specific year
  • Excel Data validation must not contain

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.

Worked Example:   Lookup lowest value in Excel

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.

Worked Example:   Calculate cap percentage at specific amount in Excel

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.

Worked Example:   How to get relative row numbers in a range in Excel

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:

Weighted average in Excel

Next Post:

Win loss points calculation 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

  • Complete List of Excel Logical Functions, References and Examples
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • How to use IFS function in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples

Date Time

  • Basic timesheet formula with breaks in Excel
  • Get days between dates in Excel
  • Convert decimal seconds to Excel time
  • Custom weekday abbreviation in Excel
  • How to calculate next scheduled event in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Group numbers at uneven intervals in Excel
  • Map text to numbers in Excel
  • Calculate conditional mode with criteria in Excel
  • How to randomly assign data to groups in Excel

General

  • How to generate random number weighted probability in Excel
  • How to get Excel workbook path only
  • List worksheet index numbers in Excel
  • Currency vs Accounting Format in Excel
  • Check if multiple cells have same value with case sensitive in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning