Index Match with multiple conditions

ArviLaanemets · #1 12-11-2023, 04:27 AM

Something like this?

I added a column for numbering rows into source table, and removed spaces from 2 column names there. And placed the result table on separate sheet.

On sheet with result table, I moved dates to Named Range at top of sheet, and added non-formula headers to result table, which allowed the result table to be defined as Table too.

Agnieszka · #2 12-11-2023, 06:21 PM

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

ArviLaanemets · #3 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))

Agnieszka · #4 12-12-2023, 01:32 AM

Quote:

Originally Posted by ArviLaanemets

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

Agnieszka · #5 12-11-2023, 06:23 PM

@p45cal Pivot table is not refreshing with new information when I update source data. Additionally I can't add more lines to the source data. DO you know how I can fix this?

p45cal · #6 12-11-2023, 06:55 PM

Quote:

Originally Posted by Agnieszka

@p45cal Pivot table is not refreshing with new information when I update source data. Additionally I can't add more lines to the source data. DO you know how I can fix this?

On examining your file more closely, it looks as though you might be using Excel for Mac; can you confirm which version of Excel you're using?

Agnieszka · #7 12-11-2023, 08:25 PM

Quote:

Originally Posted by p45cal

On examining your file more closely, it looks as though you might be using Excel for Mac; can you confirm which version of Excel you're using?

I need to be able to run this report on my work computer, not Mac. We use Excel 2016.

p45cal · #8 12-12-2023, 04:50 AM

Quote:

Originally Posted by Agnieszka

I need to be able to run this report on my work computer, not Mac. We use Excel 2016.

Two things:
1. As I mentioned before: "If the source data changes, the table (a pivot table) will need refreshing (right-click it and choose Refresh).". This is not automatic (but it can be made to be).
2. If you're using Excel 2016 for Windows my attachment to post #3 should work.
Adding rows to the source data should be as easy as writing new entries immediately below the existing table, or pasting data to the same place. Just make sure the table extents cover your data (a small symbol in the bottom right corner of the bottom right cell of the table is apparent and you can click and drag it to cover your data, but this shouldn't be necessary since the table should adjust its size automatically.

I know nothing about the Mac versions of Excel but I suspect they don't have the Data Model. So I'll wait until you try it at work. Try the file I attached directly at work, don't use one you've first opened and saved on your Mac.

Agnieszka · #9 12-13-2023, 12:50 AM

Quote:

Originally Posted by p45cal

Two things:
1. As I mentioned before: "If the source data changes, the table (a pivot table) will need refreshing (right-click it and choose Refresh).". This is not automatic (but it can be made to be).
2. If you're using Excel 2016 for Windows my attachment to post #3 should work.
Adding rows to the source data should be as easy as writing new entries immediately below the existing table, or pasting data to the same place. Just make sure the table extents cover your data (a small symbol in the bottom right corner of the bottom right cell of the table is apparent and you can click and drag it to cover your data, but this shouldn't be necessary since the table should adjust its size automatically.

I know nothing about the Mac versions of Excel but I suspect they don't have the Data Model. So I'll wait until you try it at work. Try the file I attached directly at work, don't use one you've first opened and saved on your Mac.

Hi, thank you for your help, I will try to open your spreadsheet on device with Windows PC just need to find one. My work computer will not connect to external sources due to server protection.
I tried to create new spreadsheet and create pivot table coping what you have done. Could you let me know how did you create Data label to include all dates and txt EID (fx)?

p45cal · #10 12-13-2023, 03:08 AM

Quote:

Originally Posted by Agnieszka

Could you let me know how did you create Data label to include all dates and txt EID (fx)?

That would take me ages!
Perhaps download the file I attached in msg#3 to your computer at home but do not open it. Email yourself to your work email and attach that file. At work, open the file in Excel. Otherwise, copy the file to a memory stick and take it to work. There are no macros in the workbook.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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