FG
💻 Software

How do I get a reference to the last occurrence of a value in an array in Excel?

Fresh5 days ago
Mar 15, 202610929 views
Confidence Score0%
0%

Problem

So if A1:A5 = {3, 3, 4, 4, 5} what function could I put into B1 to get a reference to A4 (the cell containing the last occurrence of 4)?

Unverified for your environment

Select your OS to check compatibility.

1 Fix

Canonical Fix
Unverified Fix
New Fix – Awaiting Verification

Fix for: How do I get a reference to the last occurrence of a value in an array in Excel?

Low Risk

I'm assuming that A1:A5 are sorted in ascending order. If that is the case, you can use the following formula: This will give an output of $A$4. Here's how it works: finds the index of the largest value that is <= 4, assuming that A1:A5 is sorted in…

Awaiting Verification

Be the first to verify this fix

Sign in to verify this fix

Environment