Index Match with multiple conditions

ArviLaanemets · #4 12-11-2023, 11:51 PM

Quote:

Originally Posted by Agnieszka

@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))

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
Vlookup or Index/Match - Multiple Criteria	ruci1225	Excel	1	01-15-2012 07:31 AM