I'm working with a HUGE set of data. Column A is years from 1990 - 2010. There are multiple instances of each year, for example, there are roughly 1500 1990's in column A that has data in other columns that corresponds to that specific 1990.

I need a formula that will give me the cell reference of the first 1990 in column A and a cell reference for the last 1990 in column A so that I can perform calculations based on that range. I would need to be able to alter this formula so that I can then get cell references for the first and last occurrence of each year so that I can make calculations based on the ranges of each year.

Here is a sample screenshot of my data for reference.

# Excel Search Function

Page 1 of 1## 6 Replies - 611 Views - Last Post: 24 May 2013 - 09:44 AM

##
**Replies To:** Excel Search Function

### #2

## Re: Excel Search Function

Posted 24 May 2013 - 08:39 AM

If the table remains in sorted order (by year) you could use

to return the first row number and

to return the last.

But what's wrong with a Pivot Table? Even using COUNTIF, SUMIF (or database functions) wouldn't require you to manually determine these row-numbers.

**=MATCH(1990,A3:A2000,0)**to return the first row number and

**=MATCH(1990,A3:A2000,1)**to return the last.

But what's wrong with a Pivot Table? Even using COUNTIF, SUMIF (or database functions) wouldn't require you to manually determine these row-numbers.

This post has been edited by **andrewsw**: 24 May 2013 - 08:40 AM

### #3

## Re: Excel Search Function

Posted 24 May 2013 - 08:51 AM

I'm not sure how I would use COUNTIF or SUMIF with the type of answers I need.

For instance, one of my questions is to determine the average CO2 emissions for each year due to coal. So would I do something like this?

=IF(1990,AVERAGEIF(D:D,"Coal",E:E))

For instance, one of my questions is to determine the average CO2 emissions for each year due to coal. So would I do something like this?

=IF(1990,AVERAGEIF(D:D,"Coal",E:E))

### #4

## Re: Excel Search Function

Posted 24 May 2013 - 09:08 AM

If you are using Excel 2007+ then

but this is definitely screaming "pivot table"!

**=AVERAGEIFS(E3:E2000,A3:A2000,1990,D3:D2000,"Coal")**but this is definitely screaming "pivot table"!

### #5

## Re: Excel Search Function

Posted 24 May 2013 - 09:11 AM

Perfect! That =AVERAGEIFS formula is exactly what I needed. Never new about it. For future reference though, what is a pivot table?

Page 1 of 1