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**is subtle yet significant. You’ll see later.*

**reference**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.

## 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:

- 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.
- This formula works with text or numbers.
- 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 3^{rd} 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 3^{rd} cell in the range C2:C13 and it contains the number 3.

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.

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 thefrom a cell or avalueto a cell is subtle yet significant”reference

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.

## Share This

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.

JC says

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.

Catalin Bombea says

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: http://www.myonlinetraininghub.com/helpdesk/

Thank you,

Catalin

Juan says

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

Mynda Treacy says

Hi Juan,

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

=INDEX(C$2:C$13,MATCH(TRUE,

,0))C$2:C$13<>“”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:

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:

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.

Mohamed says

Fantastic! Thanks for the assiduous job ma’am!

Mynda Treacy says

You’re welcome, Mohamed 🙂

Raymond Gota Toudji says

Hoi Mynda,

Thx for this well explained tutorial, as always 🙂

Gr,

Raymond.

Mynda Treacy says

Thanks, Raymond 🙂