Microsoft Office Forums Index Match Function with a Date
 Register FAQ Search Today's Posts Mark Forums Read

#1
01-07-2023, 02:07 AM
 Priyantha Gamini Windows 10 Office 2016 Novice Join Date: Dec 2022 Posts: 9
Index Match Function with a Date

Dear All,

I use the IndexMatch function to get data from an excel sheet ("Data"). According to my workbook, I hope to get data to the "B5" Cell in the "Calculation" Sheet matching "B1" & "B3" Cell values from the "Data" Sheet. I try to get only Year & Month from B3 Cell to match with column "A" in the "Data" Sheet. I use the below formula but not working correctly.

=INDEX(Data!\$D:\$D,MATCH(1,INDEX((B1=Data!\$B:\$B)*(B 3=Data!\$A:\$A),0,1),0))

can anyone help me?

Thanks

Priyantha.
Attached Files
 Increment.xlsx (15.3 KB, 5 views)
#2
01-07-2023, 02:16 AM
 Pecoflyer Windows 10 Office 2021 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,667

And what is the expected answer in B5 ?

(BTW using entire column/row referencing is bad practice)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
#3
01-07-2023, 06:49 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 719

maybe:
Code:
`=INDEX(Data!\$D\$2:\$D\$13,MATCH(1,(YEAR(Data!\$A\$2:\$A\$13)=YEAR(\$B\$3))*(MONTH(Data!\$A\$2:\$A\$13)=MONTH(\$B\$3))*(Data!\$B\$2:\$B\$13=\$B\$1),0))`
?
#4
01-07-2023, 08:13 AM
 Priyantha Gamini Windows 10 Office 2016 Novice Join Date: Dec 2022 Posts: 9

Quote:
 Originally Posted by Pecoflyer And what is the expected answer in B5 ? (BTW using entire column/row referencing is bad practice)
I hope to get paid salary for the increment date ( if emloyee incremen date is 2022/10/20, then hope to get 2022 october month paid salary)

Thanks
#5
01-07-2023, 09:00 PM
 Priyantha Gamini Windows 10 Office 2016 Novice Join Date: Dec 2022 Posts: 9

Quote:
 Originally Posted by p45cal maybe: Code: `=INDEX(Data!\$D\$2:\$D\$13,MATCH(1,(YEAR(Data!\$A\$2:\$A\$13)=YEAR(\$B\$3))*(MONTH(Data!\$A\$2:\$A\$13)=MONTH(\$B\$3))*(Data!\$B\$2:\$B\$13=\$B\$1),0))` ?
This code works after modifying it as below.

=INDEX(Data!\$D\$2:\$D\$13,MATCH(1,INDEX((YEAR(Data!\$A \$2:\$A\$13)=YEAR(\$B\$3))*(MONTH(Data!\$A\$2:\$A\$13)=MONT H(\$B\$3))*(Data!\$B\$2:\$B\$13=\$B\$1),0,1),0))

Thank..
#6
01-08-2023, 05:38 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 719

Quote:
 Originally Posted by Priyantha Gamini This code works after modifying it as below. =INDEX(Data!\$D\$2:\$D\$13,MATCH(1,INDEX((YEAR(Data!\$A \$2:\$A\$13)=YEAR(\$B\$3))*(MONTH(Data!\$A\$2:\$A\$13)=MONT H(\$B\$3))*(Data!\$B\$2:\$B\$13=\$B\$1),0,1),0))
That's crazy! Are you using Excel? What version?
Indexing twice is pointless:
2023-01-08_123457.png
#7
01-09-2023, 04:34 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 768

Much easier and more flexible way
Code:
`=SUMIFS(Data!\$D\$2:\$D\$13,Data!\$B2:\$B13,Calculation!\$B\$1,Data!\$A\$2:\$A\$13,Calculation!\$B\$3)`
Define fields Calculation!\$B\$1 and Calculation!\$B\$5 as Names, like nPFNo and nDate - the same formula will be
Code:
`=SUMIFS(Data!\$D\$2:\$D\$13,Data!\$B2:\$B13,nPFNo,Data!\$A\$2:\$A\$13,nDate)`
Define the Table on sheet Data as Defined Table, eg. tData, and the same formula will be
Code:
`=SUMIFS(tData[Salary],tData[[PF No]],nPFNo,tData[Month],nDate)`
Replace column header [PF No] in tData Table with PF, and check out the formula in Table syntax for Salary in Calculation Sheet - you see the formula was changed properly too!
#8
01-09-2023, 04:59 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 719

From:
Quote:
 Originally Posted by Priyantha Gamini I try to get only Year & Month from B3 Cell to match with column "A" in the "Data" Sheet.
I took it that datewise, the OP wanted only a month and year match, so that dates didn't need to be the first of the month everywhere.
#9
01-09-2023, 06:44 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 768

Quote:
 Originally Posted by p45cal I took it that datewise, the OP wanted only a month and year match, so that dates didn't need to be the first of the month everywhere.
All OP's month values in Data sheet were 1st of month, so it was logical to assume this is a part of routine. In case it is not the case, OP can have a calculated column which returns the 1st of month based on date in column Month in SUMIFS formula. Or OP can redesign the table, and use instead date values for months integer values in format yyyymm.

OP can also take some 15 years back (Into time when there wasn't any SUMIFS/COUNTIFS functions in Excel, and SUMPRODUCT was used instead - with a bonus that you could refer to calculations instead columns)
Code:
`=SUMPRODUCT(--(YEAR(Data!\$A\$2:\$A\$13)=YEAR(Calculation!\$B\$3)),--(MONTH(Data!\$A\$2:\$A\$13)=MONTH(Calculation!\$B\$3)),--(Data!\$B\$2:\$B\$13=Calculation!\$B\$1),Data!\$D\$2:\$D\$13)`
#10
02-03-2023, 10:39 PM
 Priyantha Gamini Windows 10 Office 2016 Novice Join Date: Dec 2022 Posts: 9

Quote:
 Originally Posted by ArviLaanemets All OP's month values in Data sheet were 1st of month, so it was logical to assume this is a part of routine. In case it is not the case, OP can have a calculated column which returns the 1st of month based on date in column Month in SUMIFS formula. Or OP can redesign the table, and use instead date values for months integer values in format yyyymm. OP can also take some 15 years back (Into time when there wasn't any SUMIFS/COUNTIFS functions in Excel, and SUMPRODUCT was used instead - with a bonus that you could refer to calculations instead columns) Code: `=SUMPRODUCT(--(YEAR(Data!\$A\$2:\$A\$13)=YEAR(Calculation!\$B\$3)),--(MONTH(Data!\$A\$2:\$A\$13)=MONTH(Calculation!\$B\$3)),--(Data!\$B\$2:\$B\$13=Calculation!\$B\$1),Data!\$D\$2:\$D\$13)`
Thanks for all of you

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post mismag Excel 5 05-29-2019 11:26 AM paulzy95 Excel 10 09-29-2016 10:46 PM shay_mt Excel 2 04-27-2015 06:04 AM jackzha Excel 5 12-03-2014 12:43 PM FraserKitchell Excel 2 01-19-2010 09:38 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:50 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top