#1




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. 
#2




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




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




Quote:
Thanks 
#5




Quote:
=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




Quote:
Indexing twice is pointless: 20230108_123457.png 
#7




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) Code:
=SUMIFS(Data!$D$2:$D$13,Data!$B2:$B13,nPFNo,Data!$A$2:$A$13,nDate) Code:
=SUMIFS(tData[Salary],tData[[PF No]],nPFNo,tData[Month],nDate) 
#8




From:
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




Quote:
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




Quote:

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Index and Match Function  mismag  Excel  5  05292019 11:26 AM 
Excel  Index and Match Function ,First Second and Third Match  paulzy95  Excel  10  09292016 10:46 PM 
Index Match Function across different worksheets  shay_mt  Excel  2  04272015 06:04 AM 
Index Match function  jackzha  Excel  5  12032014 12:43 PM 
Moving a Index/Match function  FraserKitchell  Excel  2  01192010 09:38 AM 