Index Match with multiple conditions

Agnieszka · #1 12-11-2023, 12:38 AM

Hi everyone,

Could you help me with attached spreadsheet?
On the left there is table with employees and leave they have approved.
On the right, I need to make a visual representation of it. I would like for the code from Column F to appear in Cell J2 if Employee with EID 1 (search must be by EID) was on leave 11 Dec 23.
I think this can be done with Index Match but I'm not able to do it myself.
I have manually entered first line for as an example of outocme.
Thank you for your time

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

p45cal · #3 12-11-2023, 07:15 AM

A different approach.
See table at cell H26.
I changed a date (cell E3) in the source data to cause overlaps (I know, it may never happen) so you can see what happens in the results (T28:V28).
If the source data changes, the table (a pivot table) will need refreshing (right-click it and choose Refresh).

[The whole thing is a Power Query on your source data, which loads to the Pivot Table, with the data added to the Data Model so that a measure (txt) can be added and used in the Values area of the Pivot table.]

Agnieszka · #4 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.

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.

ArviLaanemets · #8 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 · #9 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

ArviLaanemets · #10 12-12-2023, 02:17 AM

Can't help with MAC - haven't never used it. What character coding is MAC using - ASCII or something other? I'm asking, because you mentioned Excel asking about you wanting to enter formula. Suspiciously like it doesn't recognize '='!

You use both MAC and Windows computers? Have you tried do all (started from entering formulas) using Windows computer only?

p45cal · #11 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 · #12 12-13-2023, 12:41 AM

Quote:

Originally Posted by ArviLaanemets

Can't help with MAC - haven't never used it. What character coding is MAC using - ASCII or something other? I'm asking, because you mentioned Excel asking about you wanting to enter formula. Suspiciously like it doesn't recognize '='!

You use both MAC and Windows computers? Have you tried do all (started from entering formulas) using Windows computer only?

Hi, I tried doing everything from the start but some of the things you did are happening in the background that I can't work out. As advised by p45cal I will try to download files directly to Windows computer. Maybe that will make difference. Can't do it right now as my work uses protected server that will not allow me to access external forums.
Just about the information disappearing from the table - the formulas are still there, just results are hidden. I know they are there as they flash momentarily when the new data is entered.

Agnieszka · #13 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)?

ArviLaanemets · #14 12-13-2023, 01:47 AM

Quote:

Originally Posted by Agnieszka

Hi, I tried doing everything from the start but some of the things you did are happening in the background that I can't work out.

The only thing not entered directly into formula is defining the Name nDates, which represents the range Report!$C$1:$L$1. In my Excel, I can define name selecting from Main menu Formulas>[Name Manager]. Btw. There you'll find also all Defined Tables, and all DataRanges of ODBC queries in case you have defined them for this file.

Btw. I added here an example, how make it even more 'background'

p45cal · #15 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.

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