Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Index Match with multiple conditions
Register FAQ

Reply
 
Thread Tools Display Modes
  #1  
Old 12-11-2023, 06:21 PM
Agnieszka Agnieszka is offline Index Match with multiple conditions Mac OS X Index Match with multiple conditions Office 2016 for Mac
Novice
Index Match with multiple conditions 		 
Join Date: Apr 2023
Posts: 15
Agnieszka is on a distinguished road
Default

@ArviLaanemets Do you know why the result table looses all the returns when I make any changes to LeaveRep table? When I update EID or try to add more lines, all results are disappearing. I can't even get them back by reversing my actions. Need to close and re-open workbook.
Reply With Quote
  #2  
Old 12-11-2023, 11:51 PM
ArviLaanemets ArviLaanemets is offline Index Match with multiple conditions Windows 8 Index Match with multiple conditions Office 2016
Expert
  
Join Date: May 2017
Posts: 960
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default
Quote:
Originally Posted by Agnieszka View Post
@ArviLaanemets Do you know why the result table looses all the returns when I make any changes to LeaveRep table?
Nothing like this is happening for me! Changed EID's (tested it with both Tables), changed leave dates, added empty and non-empty rows, etc. All changes (which must to change the Report Table) were shown immediately. Btw., I did all this on sheets LeaveRep and Report!

Only thing I did find that must be changed is for case the same date has response for more than 1 row (leave entries are overlapping). When this happens, either an error is returned, or in worst case the code from wrong row on LeaveRep sheet is returned. So the formula for all date columns on Report sheet must be like:
Code:
=IF(COUNTIFS(Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))>1;"overlapping";
IF(SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))=0;"";
INDEX(Leave_ReportX[C_ode];SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2)))))
You having a polish name, I didn't edit the formula and left semicolons as parameter separators. In case I was wrong, replace them with commas before copying the formula into table columns.
NB! As this site interpreted field name Code put in brackets as the code tag in posting, I had to replace the Code with C_ode. Before using the formula, correct this!

The formula is somewhat long, so in case you want to use it, define some Dynamic Names (activate the top datarange cell in Date01 column of Report Table before defining them) and use them there. Like
Code:
nOverLap = COUNTIFS(Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))>1

nEmpty = SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))=0

nRepCode = INDEX(Leave_ReportX[C_ode];SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2)))

And the formula will then be like:
=IF(nOverlap;"overlapping",IF(nEmpty;"";nRepCode))
Reply With Quote
  #3  
Old 12-12-2023, 01:32 AM
Agnieszka Agnieszka is offline Index Match with multiple conditions Mac OS X Index Match with multiple conditions Office 2016 for Mac
Novice
Index Match with multiple conditions 		 
Join Date: Apr 2023
Posts: 15
Agnieszka is on a distinguished road
Default
Quote:
Originally Posted by ArviLaanemets View Post
Nothing like this is happening for me! Changed EID's (tested it with both Tables), changed leave dates, added empty and non-empty rows, etc. All changes (which must to change the Report Table) were shown immediately. Btw., I did all this on sheets LeaveRep and Report!

Only thing I did find that must be changed is for case the same date has response for more than 1 row (leave entries are overlapping). When this happens, either an error is returned, or in worst case the code from wrong row on LeaveRep sheet is returned. So the formula for all date columns on Report sheet must be like:
Code:
=IF(COUNTIFS(Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))>1;"overlapping";
IF(SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))=0;"";
INDEX(Leave_ReportX[C_ode];SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2)))))
You having a polish name, I didn't edit the formula and left semicolons as parameter separators. In case I was wrong, replace them with commas before copying the formula into table columns.
NB! As this site interpreted field name Code put in brackets as the code tag in posting, I had to replace the Code with C_ode. Before using the formula, correct this!

The formula is somewhat long, so in case you want to use it, define some Dynamic Names (activate the top datarange cell in Date01 column of Report Table before defining them) and use them there. Like
Code:
nOverLap = COUNTIFS(Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))>1

nEmpty = SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2))=0

nRepCode = INDEX(Leave_ReportX[C_ode];SUMIFS(Leave_ReportX[RowNo];Leave_ReportX[EID];[@EID];Leave_ReportX[BeginDate];"<=" & INDEX(nDates;COLUMN()-2);Leave_ReportX[EndDate];">=" & INDEX(nDates;COLUMN()-2)))

And the formula will then be like:
=IF(nOverlap;"overlapping",IF(nEmpty;"";nRepCode))
Thank you for your help!!
I see that I can make changes to the report when working on Mac but results are disappearing on Windows PC - not sure what is happening there but I can do this on my personal computer.
Although I really like the idea of report telling me when someone has couple of different leaves approved over the same period, I couldn't make the formula work for me. Made all the changes (code and commas) but still excel is asking me whether I am trying to write formula. My brain is definitely built-in with anty-excel functions
Yes, originally I'm form Poland
Reply With Quote
Reply

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple match values for INDEX(MATCH) formula jn82740 Excel 1 06-24-2023 11:48 PM
Excel Index and Match function with multiple criteria not searching the next record mushtaqkadar Excel 3 06-17-2017 12:20 AM
UDF multiple IFs INDEX-MATCH grexcelman Excel Programming 0 02-22-2015 04:20 PM
Help with multiple match and index formula ryanwood Excel 1 09-12-2012 07:53 AM
Index Match with multiple conditions Vlookup or Index/Match - Multiple Criteria ruci1225 Excel 1 01-15-2012 07:31 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:41 AM.

Contact Us - Privacy Statement - Top

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