• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Return the First and Last Values in a Range

You are here: Home / Excel Formulas / Return the First and Last Values in a Range
Return the First and Last Values in a Range
October 9, 2013 by Mynda Treacy

I’ve been asked many times how to find either the cell reference of the first or last value in a range, or even return the values from those cells, and there are many ways to do it.

As usual I'm going to share the methods I think are the best.

Note: The difference between returning the value or cell reference is subtle yet significant. You’ll see later.

Here’s our range. You can see that column C contains numbers and column D contains both numbers and text, and both columns contain blanks.

Excel find first or last value in a range

Find the First Value in a Range

Like I said, I’ve seen many ways to find the first value in a range but one formula stands out from the rest for its simplicity.

Drum roll…..

=INDEX(C$2:C$13,MATCH(TRUE,C$2:C$13<>"",0))

Entered with CTRL+SHIFT+ENTER as it’s an array formula.

Why it’s so special:

  1. The INDEX function returns a reference to a cell and when used on its own, like the above example, it returns the value in that cell.
  2. This formula works with text or numbers.
  3. The blanks are handled by the logical test in the MATCH part of the formula: C$2:C$13<>""

How it Works

If you’re new to the INDEX & MATCH dynamic duo take a few moments to read this tutorial first. These are two 'must know' power functions.

The MATCH function is the star in this formula:

MATCH(TRUE,C$2:C$13<>"",0)

In English it reads:

Tell me what position (in the range C2:C13) the first TRUE result is in by testing each cell in the range C2:C13 to see if they are not (<>) blank (""), return TRUE for cells that aren’t blank and FALSE for cells that are blank, match it exactly.

If we look at how the MATCH function evaluates it looks like this:

Step 1: Evaluate the logical test and return an array of TRUE and FALSE values:

MATCH(TRUE, {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE},0)

Step 2: Return the position number of the first ‘TRUE’ result in the array:

3

That is the first TRUE is the 3rd item in the array returned by the logical test.

If we give this result to the INDEX formula we get:

=INDEX(C$2:C$13,3)

Which evaluates to cell C4 and coincidently the value in C4 is 3.

That is cell C4 is the 3rd cell in the range C2:C13 and it contains the number 3.

INDEX finds first value in range

That’s it! Done.

Find the Last Value in a Range

Unfortunately finding the last value in a range isn’t quite as straight forward as finding the first, but it’s not too tricky.

=INDEX(C$2:C$13,MAX(IF(C$2:C$13<>"",ROW($A$1:$A$12))))

Again entered with CTRL+SHIFT+ENTER as it’s an array formula.

Let’s zoom in on the MAX(IF part of the formula:

MAX(IF(C$2:C$13<>"",ROW($A$1:$A$12)))

In English:

Test the cells in the range C2:C13 for blanks, if they’re not blank i.e. TRUE then return the corresponding number from the array of values returned by the ROW function, now just tell me the MAX value.

It evaluates like so:

Step 1 - evaluate the logical test:

MAX(IF({FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},ROW($A$1:$A$12)))

Step 2 - evaluate the ROW function:

MAX(IF{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12}))

Step 3 - complete the IF formula by replacing the TRUE’s with the corresponding numbers returned by the ROW function:

MAX({FALSE;FALSE;3;4;FALSE;6;FALSE;7;FALSE;10;FALSE;FALSE})

Step 4 – find the MAX of the values:

10

Now we can give that 10 to INDEX and we get this:

=INDEX(C$2:C$13,10)

=C11, and the value in C11 is 4.

Excel find last value in range using INDEX

This formula works for both text and numbers.

Find the Last Number in a Range

If your range contains only numbers, you can use this formula to find the last cell containing a number in the range:

=INDEX(C$2:C$13,MATCH(1E+100,C$2:C$13,1))

Note: for a change this isn’t an array formula.

The trick with this formula is the 1E+100.

1E+100 is just a really big number. You can use any number here as long as it is bigger than any number in your range.

Side note: You might have seen 9.99999999999999E+307 used in formulas before and that's because it's the biggest number you can enter into Excel.

The problem with 9.999 blah, blah, blah is that I find it really hard to remember how many 9's to enter. Whilst 1E+100 is not the biggest number, it is still big enough to work in most scenarios and comes with the bonus that it's a whole load easier to remember.

Ok, sorry I digress... when you ask MATCH  to find a number that is bigger than any of the numbers in your range it will return the position of the last value in the range when you use 1 (for less than) as the 'match_type' argument.

Remember the syntax for MATCH is

MATCH(lookup_value, lookup_array, [match_type])

So in English MATCH(1E+100,C$2:C$13,1) reads:

Lookup the biggest number you can imagine, in the range C2:C13, if you can't find it then return the position of the last number you find.

Remember this formula only works with numbers, not text.

Using INDEX to Return a Reference

So, what about that comment I made in the beginning:

“The difference between returning the value from a cell or a reference to a cell is subtle yet significant”

Well, since we know that the INDEX function returns a reference to a cell we can use the above formulas to return a range that starts with the first occupied cell and ends with the last.

To do this we simply join the two formulas together with a colon, which is the range operator:

INDEX(C$2:C$13,MATCH(TRUE,C$2:C$13<>"",0)):INDEX(C$2:C$13, MATCH(1E+100,C$2:C$13,1))

Remember the first INDEX formula returns a reference of C4 and the second returns C11 so the above formula returns the following range:

C4:C11

What’s the point when you could just enter C4:C11? Well, because using INDEX gives us a dynamic range that will grow or shrink with your data.

Don’t forget if your range contains text you need to use the MAX(IF version:

=INDEX(C$2:C$13,MATCH(TRUE,C$2:C$13<>"",0)):INDEX(C$2:C$13,MAX(IF(C$2:C$13<>"", ROW($A$1:$A$12))))

In most cases you’ll need to enter a formula containing either of the above dynamic ranges using CTRL+SHIFT+ENTER, or you can set it up as a dynamic named range for use in your formulas.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Return the First and Last Values in a Range

More Dynamic Named Range Posts

More Index & Match Posts

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel FormulasTag: dynamic named range, index & match
Previous Post:Excel Average IF Excluding OutliersExcel Average IF Excluding Outliers
Next Post:VBA to Create PDF from Excel Worksheet Then Email It With OutlookVBA to Create PDF from Excel Worksheet Then Email It With Outlook

Reader Interactions

Comments

  1. Ian Iver

    November 26, 2020 at 9:58 pm

    I think finding the first number can be done a little bit simpler, and by also ignoring text values. A range formula of =MATCH(2,ISNUMBER(C$2:C$13)+1,0) should do the trick.
    ISNUMBER returns a boolean value of 0 for false and 1 for true. Add 1 to change the output from boolean to a number. Then you are just looking for the first iteration of “2”.
    Like I said before, this can work even if you have text cells in the range you are looking for – plus is a shorter formula.

    Reply
    • Mynda Treacy

      November 27, 2020 at 12:34 pm

      Nice alternative, Ian. Obviously to return the value, as opposed to the row number containing the value, you still need INDEX like so:

      =INDEX(C$2:C$13,MATCH(2,ISNUMBER(C$2:C$13)+1,0))

      And for a column containing only text it would be:

      =INDEX($D$2:$D$13,MATCH(2,ISTEXT(D$2:D$13)+1,0))

      But for a column containing a mix of text and numbers, you still need this formula:

      =INDEX(D$2:D$13,MATCH(TRUE,D$2:D$13<>"",0))

      Mynda

      Reply
  2. Marc

    November 12, 2020 at 12:12 pm

    I love that this article is still producing results after 7 years! It was short and to the point. Thank you.

    Reply
    • Mynda Treacy

      November 12, 2020 at 12:41 pm

      So pleased to hear that, Marc!

      Reply
  3. Sreekumar

    October 12, 2019 at 6:40 pm

    I have a table which contains the employees age in range against the salary range and wanted to retrieve the number of employees with the age against the salary

    Ages Salary
    15-25 26-30 31-3
    18-25 1 2 3
    26-30 4 5 6
    31-35 7 8 9
    Require excel formula when the age and salary is entered, the cell value age and salary of the corresponding value gets displayed

    Example
    Input
    Age 29
    Sal 29

    Output 5

    Reply
    • Catalin Bombea

      October 13, 2019 at 12:44 pm

      Hi Sreekumar,
      It’s the third time you ask the same question without clarifying the details. Your data structure does not make sense, this is the reason you have to sign-up to our forum, create a new topic and upload a sample file and provide more details. Here is the link: https://www.myonlinetraininghub.com/excel-forum/

      Reply
  4. David N

    June 22, 2017 at 6:53 am

    I know this is an older post, but I haven’t seen anything in newer ones that mentions these tips. So I thought I would share them.

    The First Value formula could be made non-array by including an extra INDEX:
    =INDEX(C$2:C$13,MATCH(TRUE,INDEX(C$2:C$13″”,0),0))

    Finding the Last Text (instead of the Last Number) can be done with an omega, which is really just a bigger character (ASCII value) than anything on the normal keyboard:
    =INDEX(C$2:C$13,MATCH(“Ω”,C$2:C$13,1))
    The omega is entered using Alt+234, where the 234 must be pressed in sequence on the numeric keypad, holding down Alt throughout.

    And finding the Last Value (regardless of text or numeric) can be done in newer Excel versions as a non-array formula with the AGGREGATE function:
    =INDEX(C$2:C$13,AGGREGATE(14,6,ROW($A$1:$A$12)/(C$2:C$13″”),1))

    Reply
    • Mynda Treacy

      June 22, 2017 at 1:43 pm

      Nice tips, David. I’ve not seen the omega trick before. Thanks for sharing.

      Mynda

      Reply
  5. JC

    January 25, 2014 at 5:43 am

    Can you use INDEX and MATCH to return the value of a cell in column A if it matches a condition in Column C and then be able to program the formula to return the next cell in Column A if it again matches a condition in Column.

    Like if Columna A is an ascending order of numbers and Column C is THIS or THAT and I need to be able to return any number in Column A that matches THAT in Column C.

    Reply
    • Catalin Bombea

      January 25, 2014 at 12:37 pm

      Hi JC,
      Can you prepare a sample workbook with your data and details on what you are trying to do? I did not understand what you mean by “Column C is THIS or THAT”, it will be easier to work on your data.
      You can use our Help Desk: https://www.myonlinetraininghub.com/helpdesk/
      Thank you,
      Catalin

      Reply
      • Juan

        February 14, 2014 at 11:28 am

        Why is the first formula =INDEX(C$2:C$13,MATCH(TRUE,C$2:C$13″”,0)) an array formula? Great tutorial, Mynda, you have the skill to explain the most difficult things in a way we love to understand

        Reply
        • Mynda Treacy

          February 14, 2014 at 12:54 pm

          Hi Juan,

          In the first formula the MATCH function is handling a logical test:

          =INDEX(C$2:C$13,MATCH(TRUE,C$2:C$13<>“”,0))

          i.e. test if the values in C2:C13 are not equal to “” (“” is an empty cell).

          This returns an array of TRUE and FALSE values that look like this:

          MATCH(TRUE, {FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE},0)

          In order for Excel to handle this array we must enter the formula with CTRL+SHIFT+ENTER to tell it that this is an array formula.

          However, in the last formula above which finds the last number in a range:

          =INDEX(C$2:C$13,MATCH(1E+100,C$2:C$13,1)) 

          We use the MATCH function in a different way; we simply ask it to find the last number in the range that is smaller than 1E+100. There is no logical test and so MATCH simply returns a single result and we don’t need to enter it as an array formula.

          You can read more on array formulas here.

          I hope that helps.

          Mynda.

          Reply
  6. Mohamed

    December 30, 2013 at 6:15 am

    Fantastic! Thanks for the assiduous job ma’am!

    Reply
    • Mynda Treacy

      December 30, 2013 at 8:01 pm

      You’re welcome, Mohamed 🙂

      Reply
  7. Raymond Gota Toudji

    October 11, 2013 at 1:10 am

    Hoi Mynda,
    Thx for this well explained tutorial, as always 🙂
    Gr,
    Raymond.

    Reply
    • Mynda Treacy

      October 11, 2013 at 11:29 am

      Thanks, Raymond 🙂

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.