Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-07-2023, 02:07 AM
Priyantha Gamini Priyantha Gamini is offline Index Match Function with a Date Windows 10 Index Match Function with a Date Office 2016
Novice
Index Match Function with a Date
 
Join Date: Dec 2022
Posts: 9
Priyantha Gamini is on a distinguished road
Default 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
File Type: xlsx Increment.xlsx (15.3 KB, 6 views)
Reply With Quote
  #2  
Old 01-07-2023, 02:16 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Index Match Function with a Date Windows 10 Index Match Function with a Date Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 01-07-2023, 06:49 AM
p45cal's Avatar
p45cal p45cal is offline Index Match Function with a Date Windows 10 Index Match Function with a Date Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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))
?
Reply With Quote
  #4  
Old 01-07-2023, 08:13 AM
Priyantha Gamini Priyantha Gamini is offline Index Match Function with a Date Windows 10 Index Match Function with a Date Office 2016
Novice
Index Match Function with a Date
 
Join Date: Dec 2022
Posts: 9
Priyantha Gamini is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
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
Reply With Quote
  #5  
Old 01-07-2023, 09:00 PM
Priyantha Gamini Priyantha Gamini is offline Index Match Function with a Date Windows 10 Index Match Function with a Date Office 2016
Novice
Index Match Function with a Date
 
Join Date: Dec 2022
Posts: 9
Priyantha Gamini is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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..
Reply With Quote
  #6  
Old 01-08-2023, 05:38 AM
p45cal's Avatar
p45cal p45cal is offline Index Match Function with a Date Windows 10 Index Match Function with a Date Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Priyantha Gamini View Post
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
Reply With Quote
  #7  
Old 01-09-2023, 04:34 AM
ArviLaanemets ArviLaanemets is offline Index Match Function with a Date Windows 8 Index Match Function with a Date Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
  #8  
Old 01-09-2023, 04:59 AM
p45cal's Avatar
p45cal p45cal is offline Index Match Function with a Date Windows 10 Index Match Function with a Date Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

From:
Quote:
Originally Posted by Priyantha Gamini View Post
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.
Reply With Quote
  #9  
Old 01-09-2023, 06:44 AM
ArviLaanemets ArviLaanemets is offline Index Match Function with a Date Windows 8 Index Match Function with a Date Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by p45cal View Post
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)
Reply With Quote
  #10  
Old 02-03-2023, 10:39 PM
Priyantha Gamini Priyantha Gamini is offline Index Match Function with a Date Windows 10 Index Match Function with a Date Office 2016
Novice
Index Match Function with a Date
 
Join Date: Dec 2022
Posts: 9
Priyantha Gamini is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index Match Function with a Date Index and Match Function mismag Excel 5 05-29-2019 11:26 AM
Excel - Index and Match Function ,First Second and Third Match paulzy95 Excel 10 09-29-2016 10:46 PM
Index Match Function across different worksheets shay_mt Excel 2 04-27-2015 06:04 AM
Index Match Function with a Date Index Match function jackzha Excel 5 12-03-2014 12:43 PM
Moving a Index/Match function FraserKitchell Excel 2 01-19-2010 09:38 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:07 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft