Find dates with matching month and year on a row -> return value 2 columns to the right

**Cumbo** · 11-15-2012, 07:16 AM

Okay, the title probably sounds really confusing, but what I am trying to accomplish, is a formula, that would go through values (dates) on a row. When a date with same year and month is found, it would return the value two columns right. Note, that there might be several matching cells with same month/year, so it shouldn't stop on the first match found.

So far, I've tried something like this:

INDEX(A2:J2;1;MATCH(DATE(YEAR(A1);MONTH(A1);1)+2;A2:J2;0))

Note, that I am using Finnish excel, so ';' might have to be replaced with ','..

\1

Please help!

**:) Sixthsense :)** · 11-15-2012, 07:46 AM

In the first screenshot A1 cell date is a text data entered with "." instead of "-". So enter a valid date in Cell A1 and try.

**Cumbo** · 11-15-2012, 07:55 AM

Originally Posted by :) Sixthsense :)

In the first screenshot A1 cell date is a text data entered with "." instead of "-". So enter a valid date in Cell A1 and try.

What do you mean? In Finland, this is "valid" date form.

EDIT:

it works fine, when there is a date on the row with same month, year and day is "1", but I have yet to figure out how to return the value, if the day is anything else than "1".

\1

Note: "#PUUTTUU!" = "#NA!"

**Cumbo** · 11-16-2012, 07:49 AM

Any help??

**:) Sixthsense :)** · 11-17-2012, 03:49 AM

Not able to fix it and will be helpful if you attach a sample workbook.

**Cumbo** · 11-17-2012, 08:20 AM

Originally Posted by :) Sixthsense :)

Not able to fix it and will be helpful if you attach a sample workbook.

Here it is:

Table.xlsx

**:) Sixthsense :)** · 11-17-2012, 10:08 AM

Thanks for the attachment file, please let us know the expected output and the method to arrive it.

**Teethless mama** · 11-17-2012, 01:12 PM

Criteria: A1
Define name range A2:J2 - data

=IFERROR(INDEX(data,SMALL(IF(ISNUMBER(data),IF(YEAR(data)=YEAR($A$1),IF(MONTH(data)=MONTH($A$1),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(data))))+2))),COLUMNS($A:A))),"")

Ctrl+Shift+Enter, not just enter
Copy across

**Kevin UK** · 11-18-2012, 04:28 AM

Hi Cumbo

Not sure if i am on the right wave length. This will return the Euro value 2 cells to the right of the date in row 2 which corresponds to the date in A1, if no match is found then "No Match".

Formula:

Please Login or Register  to view this content.

**Cumbo** · 11-19-2012, 02:41 AM

Originally Posted by Teethless mama

Criteria: A1
Define name range A2:J2 - data

=IFERROR(INDEX(data,SMALL(IF(ISNUMBER(data),IF(YEAR(data)=YEAR($A$1),IF(MONTH(data)=MONTH($A$1),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(data))))+2))),COLUMNS($A:A))),"")

Ctrl+Shift+Enter, not just enter
Copy across

I tried this and I only got 100 €. The formula should return 300 €, as there are two dates with same month and year (December 2012) between B2:J2.

**:) Sixthsense :)** · 11-19-2012, 04:00 AM

In A2 cell

Formula:

Please Login or Register  to view this content.

**Cumbo** · 11-19-2012, 04:46 AM

Originally Posted by :) Sixthsense :)

In A2 cell

Formula:

Please Login or Register  to view this content.

600 € with this formula, too..

**:) Sixthsense :)** · 11-19-2012, 04:58 AM

When I opened your file it was showing 600 €, I just placed the cursor in A2 cell and pressed F2 and enter Its shown the 300 € as result.

**Cumbo** · 11-19-2012, 05:05 AM

Originally Posted by :) Sixthsense :)

When I opened your file it was showing 600 €, I just placed the cursor in A2 cell and pressed F2 and enter Its shown the 600 € as result.

600 € is wrong.

2.12.2012 100 €
5.12.2012 200 €
8.3.2013 300 €

--> the two dates, that have are within the same month and year as the date in A1 are 2.12.2012 and 5.12.2012 and thus the results should be (100 € + 200 € =) 300 €.

**:) Sixthsense :)** · 11-19-2012, 05:07 AM

Oops.. typo error I got 300. Now i corrected in my previous post.

**Vinoth Kanna** · 11-19-2012, 05:11 AM

When a date with same year and month is found, it would return the value two columns right.

What does ''return the value two column to the right" mean? Do you want to write a formula in one cell and get the desired value in some other cells if the conditions are satisfied? It is not possible to write a formula in one cell and get the value in another cell.
If you require the output in the fashion which i had mentioned then the situation can either be handled using VBA or please change your data in the form of a table.

Please let me know if my understanding is wrong.

**:) Sixthsense :)** · 11-19-2012, 05:15 AM

Refer the attached Screenshot for reference.

**Cumbo** · 11-19-2012, 05:19 AM

Originally Posted by Vinoth Kanna

What does ''return the value two column to the right" mean? Do you want to write a formula in one cell and get the desired value in some other cells if the conditions are satisfied? It is not possible to write a formula in one cell and get the value in another cell.
If you require the output in the fashion which i had mentioned then the situation can either be handled using VBA or please change your data in the form of a table.

Please let me know if my understanding is wrong.

The idea is simply to add all cells together, whose date is within the same month and year as the specified cell (in my table, cell A1). The date of each €-value cell is "two columns two the left of them". I.e. for the first date in column B, the corresponding € value is in column D. The second date is in column E and corresponding € value in column G etc.

**Vinoth Kanna** · 11-19-2012, 05:39 AM

If that is the case then the formula given by Sixthsense should work.

**Cumbo** · 11-19-2012, 05:50 AM

11307733.jpg

Hmmm... Doesn't work for me.

**Cumbo** · 11-19-2012, 09:04 AM

Originally Posted by Cumbo

Attachment 194734

Hmmm... Doesn't work for me.

Works now, had to replace "MMYYYY" with the Finnish version. Thanks a lot!

That was the good news and now for the other stuff:

the case I was trying to solve is more complex. For the sake of simplicity, I made another table which simplified the problem I had. I though I would get it working with the formula above, but turns out I was wrong.

In my actual table:
#1 there are more columns, from where the values must be picked to the other table
#2 the name in column B (sheet "Case") must correspond to that in column A, sheet "€"
#3 the value in column C (sheet "Case") must be "x"

What I am trying to accomplish:

Add all values from columns "Type 1 €", "Type 2 €" and "Type 3 €" (sheet "Case") on sheet "€". As mentioned above, the values added must also fulfill #2 and #3.

**:) Sixthsense :)** · 11-20-2012, 12:02 AM

In B2 cell – Non Array Formula

This formula is worked for me

Formula:

Please Login or Register  to view this content.

The below one is for you in B2 cell

Formula:

Please Login or Register  to view this content.

Drag it down and right.

**Cumbo** · 11-20-2012, 02:44 AM

Originally Posted by :) Sixthsense :)

In B2 cell – Non Array Formula

This formula is worked for me

Formula:

Please Login or Register  to view this content.

The below one is for you in B2 cell

Formula:

Please Login or Register  to view this content.

Drag it down and right.

Wow, it worked! Just had to remove one extra space inside the quote marks in the first sumproduct formula. Other than than, worked perfect. Thanks a lot!

=SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$J$2:$BG$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$K$2:$BH$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$L$2:$BI$23)

Find dates with matching month and year on a row -> return value 2 columns to the right

LinkBack

Thread Tools

Rate This Thread

Display

Find dates with matching month and year on a row -> return value 2 columns to the right

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Re: Find dates with matching month and year on a row -> return value 2 columns to the righ

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions