# Excel Search Function

Page 1 of 1

## 6 Replies - 687 Views - Last Post: 24 May 2013 - 09:44 AMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'http://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=321730&amp;s=4a2643aef2ff6a93d9005288768c7c13&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #1 TurboST2

Reputation: 0
• Posts: 37
• Joined: 11-November 11

# Excel Search Function

Posted 24 May 2013 - 08:23 AM

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.

Is This A Good Question/Topic? 0

## Replies To: Excel Search Function

### #2 andrewsw

• blow up my boots

Reputation: 6521
• Posts: 26,397
• Joined: 12-December 12

## Re: Excel Search Function

Posted 24 May 2013 - 08:39 AM

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

=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 TurboST2

Reputation: 0
• Posts: 37
• Joined: 11-November 11

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

### #4 andrewsw

• blow up my boots

Reputation: 6521
• Posts: 26,397
• Joined: 12-December 12

## Re: Excel Search Function

Posted 24 May 2013 - 09:08 AM

If you are using Excel 2007+ then

=AVERAGEIFS(E3:E2000,A3:A2000,1990,D3:D2000,"Coal")

but this is definitely screaming "pivot table"!

### #5 TurboST2

Reputation: 0
• Posts: 37
• Joined: 11-November 11

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

### #6 andrewsw

• blow up my boots

Reputation: 6521
• Posts: 26,397
• Joined: 12-December 12

## Re: Excel Search Function

Posted 24 May 2013 - 09:21 AM

### #7 TurboST2

Reputation: 0
• Posts: 37
• Joined: 11-November 11

## Re: Excel Search Function

Posted 24 May 2013 - 09:44 AM