Match and Find Multiple Cells Same Row/Column

**Cambium** · 03-13-2013, 03:41 PM

Hi all.. Thanks for having this forum available.

I'm having trouble figuring something out. I added a screen shot for easier reference.

I have 1192 Rows of weather data from 1949-2013. Each year has 12 months of data. I am trying to get just March snowfall data and enter it into a seperate cell. So for instance.. March 1949 there was 4 inches of snow. I need that "4" in a new cell.

My goal is to instantly show all the March snowfalls since 1949 instead of doing it manually. I think I am close but I cant figure out how to get it to show multiple rows. Obviously every March had different snow amounts.

So Column A and Column O will always stay the same. If we can match the "3s" in Column A and then return what is shown in Column O for that particular row, thats will give us all March snow totals for each seperate year.

Is this possible? Thanks

Excell.jpg

**majosum** · 03-13-2013, 10:35 PM

Hi,

I know how to solve it if you return all of the March snowfall values in a column instead of a row. If you are interested, let me know.

EDIT: I attached a spreadsheet that has the first two years and I included the formulas that would go under the March subtotals.
These should be set to work except you need to make one change. In the third column on my worksheet (column F), you will need to change the cell references in the second and third cells. They are referencing the cells above it, so you will need to change the "F" to whatever column you paste the formula into. After that, you can drag the formula down. The third column is just a match function that returns the position of March in column A. This column is necessary to use, but is not necessary to have visible, so you can either hide or change the font to white. Also, my functions are under the assumption that you do not have more than 1235 rows of data. If you do, that number will also need to be changed.

If you have your heart set on seeing the data horizontally, you can copy the column and paste special --> transpose to have the data stored horizontally wherever you want it.

If you can't figure it out, feel free to send me the document and I can set it up for you. Let me know if you need any help.

**Cambium** · 03-14-2013, 09:16 AM

Thanks for the reply! I will look into this when I get back. Thanks so much.

**Cambium** · 03-14-2013, 01:05 PM

Ok, I checked it out and a bit confused. To make this easier for us let me list the questions I have..

I attached my data in case you want to tackle it with me.

1. What is your "E" column for?
2. Where did the 7 & 17 numbers come from for Column "F"
3. It says "sum" in the formula, are you adding data together?
I wont want to add the totals up. Just need exact amount shown as shown in my Column O

I tried copying pasting the formula you had and did what you said changing the formula to be in the correct cell. Then tried playing with it a little and just couldnt get it..

Lets do Column W cells in my attachment as you'll see.

Let me know what you think. Thanks.

**majosum** · 03-14-2013, 06:34 PM

I completed it for you. In response to your questions:

1. What is your "E" column for?
-This column was just the column I was using in the mock workbook I created. It has no bearing on your document.

2. Where did the 7 & 17 numbers come from for Column "F"
-I left a note in the document explaining this. They are the positions in the column for each March location. The numbers have changed, but in my example, the 7th row was the first occurrence of March. The next occurrence of March was 17 more rows down.

3. It says "sum" in the formula, are you adding data together? I wont want to add the totals up. Just need exact amount shown as shown in my Column O.
-The sum is adding up the values from question 2 (the 7 and 17). The reason it is adding these up is because the second value of March is not on the 17th row; it is on the 7th + 17th row. So in my example, the first occurrence would have been on the 7th row and the second would have been on the 24th.

If you need any more elaboration, feel free to get ahold of me.

**Cambium** · 03-14-2013, 07:08 PM

This is beautiful. Wow, perfect. Now I understand how it all works. Something I wouldnt have figured out on my own or with youtube videos so thanks so much.

I even tested my knowledge by trying to get the January data and it worked. In fact, I found an error. Im missing year 1961 in my data.

I also realized that there needs to be an exact pacing with each year or same amount of rows in the data otherwise the numbers get screwy. I totally understand the 19, thanks for explaining that.

Let me play around with this a little more and if I have any other questions I'll post it. Cool stuff. Perfect. You just made my life so much easier.

**majosum** · 03-14-2013, 07:57 PM

No problem! And don't feel scared to hit the star in the lower right hand corner ;-)

Also, it does not matter if your spacing in between years is different. The formula will still find wherever March is located.