Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Find First Value In Column

1. I agreed to these rules
Join Date
12th January 2007
Posts
17

## Find First Value In Column

Hi there

This probably is an easy one, but I couldn't find it out by myself.

I've got time series data in columns which can have various #N/A cells until the first value appears. I am searching for a formula which retrieves the first value appearing in such a time series output list (column).

I know this following formula to search for the last value:

VB:
```=INDEX(M12:M500;MATCH(9.99999999999999E+307;M12:M500))

```
There must be something similar to find the first value...?

Greets, D.

Excel Video Tutorials / Excel Dashboards Reports

2. Member
Join Date
9th September 2004
Posts
88

## Re: Find First Value In Column

{=INDEX(M12:M500,MATCH(TRUE,M12:M500<>0,0))}

array formular

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
12th January 2007
Posts
17

## Re: Find First Value In Column

The formula works, but the problem with it is that I need to define an array with the same length as the data (column), so with time series data i get a few hundred cells in my array, all with the value of the first cell..

Is there any way I can eliminate this problem, with having a non-array formula only in one cell?

greets

Excel Video Tutorials / Excel Dashboards Reports

4. Established Member
Join Date
24th March 2005
Posts
507

## Re: Find First Value In Column

Originally Posted by Davidoff

The formula works, but the problem with it is that I need to define an array with the same length as the data (column), so with time series data i get a few hundred cells in my array, all with the value of the first cell..

Is there any way I can eliminate this problem, with having a non-array formula only in one cell?

greets

Try...

=INDEX(M12:500,MATCH(TRUE,INDEX(M12:M500<>0,0,0),0))

Hope it helps!
Last edited by Fin Fang Foom; March 6th, 2007 at 00:09.

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
12th January 2007
Posts
17

## Re: Find First Value In Column

And the winner is...

Fin Fang Foom!

This is great, works perfectly! Thank you so much!!!

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno