Question Excel - matrix formula (index match)

[dradenpoel]

Hey there,

I googled myself to the point where I don't find the answer and my comprehension of the formula build up is not sufficient.

I am making a dynamic lookup and connecting several data columns into an overview based upon a weeknumber.

What I have;

The list under AD5 is picked up in AK6:AK19 with following formula (which is pulled through to the next 2 cells):

=IFERROR(INDEX(employee,MATCH(0,countif($AD$5:AD5,employee)+IF(week<>$B$3,1;0)+IF(team<>$B$6,1;0);0)),"")

Named ranges in the formula;
employee: AK6:AK19
week: AI6:AI19
team: AJ6:AJ19

What I am now trying to accomplish is to also put the remark in col AE, based upon the list of names in AD.

I tried adapting the above formula as following (and pulled through to the next 2 cells)

=IFERROR(INDEX(remark,MATCH(0,countif($AE$5:AE5,remark)+IF(employee<>AD6;1;0)+IF(week<>$B$3,1;0)+IF(team<>$B$6,1;0);0)),"")

Named range added in the formula;
remark: AL6:AL19

This does not work since the matrixes are not the same anymore, which is needed for the formula to work, as I understood it while googling.

Anyone?

EDIT:
If you cannot see the images, you can find an example file here:
google sheets example file
Corrected AF to AE reference

 

[Eximo]

Can't really work this out without the actual data. I can't tell you what result Match will give without knowing the results of all the IF statements.

Not sure what you are trying to accomplish with the countif either. You are looking in a single cell for something that has a match to something in your "Remark" column. But you didn't mention what is there.

The addition should work fine if you are trying to match 0 to the sum of all your IF statements. But I am wondering if there is a much easier way if the logic boils down to true or false. Nested IF statements might be more complicated to read, but should work as well. CountIFS might do the job better as well.

Stackoverflow would be a more appropriate place to put this question.

 

[dradenpoel]

Can't really work this out without the actual data. I can't tell you what result Match will give without knowing the results of all the IF statements.

Not sure what you are trying to accomplish with the countif either. You are looking in a single cell for something that has a match to something in your "Remark" column. But you didn't mention what is there.

The addition should work fine if you are trying to match 0 to the sum of all your IF statements. But I am wondering if there is a much easier way if the logic boils down to true or false. Nested IF statements might be more complicated to read, but should work as well. CountIFS might do the job better as well.

Stackoverflow would be a more appropriate place to put this question.

All data needed is actually there.
Thought the image would make this very clear, maybe my accompanying text is insufficient.

The countif is part of the matrix formula, which makes the index formula use several arguments to give the resulting list of names.
How it works exactly I am still trying to figure out, as I stated, my comprehension of the formula is insufficient.

 

[Eximo]

If there are pictures they are not uploaded in a secure/trusted manner. Many browsers will block things they don't like.

So somewhere you have listed out what is in AE5? Or is the problem that you want to use AD5? Also not clear what is in the B column, unless it is fixed variables you have defined.

 

[dradenpoel]

If there are pictures they are not uploaded in a secure/trusted manner. Many browsers will block things they don't like.

So somewhere you have listed out what is in AE5? Or is the problem that you want to use AD5? Also not clear what is in the B column, unless it is fixed variables you have defined.

You can find an example file here:
google sheets example file

 

[dradenpoel]

Works on the sheetsfile now

=ArrayFormula(ERRORIF(INDEX(remark,MATCH(0,COUNTIF($D$5:D5,remark)+IF(employee<>C6,1,0)+IF(week<>$A$3,1,0)+ALS(team<>$A$6,1,0),0)),""))

 

[dradenpoel]

Works on the sheetsfile now

=ArrayFormula(ERRORIF(INDEX(remark,MATCH(0,COUNTIF($D$5:D5,remark)+IF(employee<>C6,1,0)+IF(week<>$A$3,1,0)+ALS(team<>$A$6,1,0),0)),""))

But does not in excel after adapting the formula for excel ...

 

[Ralston18]

Question regarding:

"What I am now trying to accomplish is to also put the remark in col AF, based upon the list of names in AD. " [My underline.]

Based on the code I think you meant AE not AF.

The remark appears to be in AE but could it be in AF?

= = = =

Also:

I see column headers for 'week' and 'team' but not 'employer'. I see column 'name' which is not in the code.

Just wondering.....

 

[dradenpoel]

Question regarding:

"What I am now trying to accomplish is to also put the remark in col AF, based upon the list of names in AD. " [My underline.]

Based on the code I think you meant AE not AF.

The remark appears to be in AE but could it be in AF?

= = = =

Also:

I see column headers for 'week' and 'team' but not 'employer'. I see column 'name' which is not in the code.

Just wondering.....

Name = Employee

 

[Ralston18]

One or the other - not both. Use either Name or Employee - no "swapping".

Such things, even if they may not or should not seemingly make a difference per se, can create confusion.

Good code requires consistency. If not then reading the code, troubleshooting, testing, and documentation will be much more difficult.

Also NAME or NAMES could be a reserved word.

Reference:

https://www.engram9.info/access-2007-vba/reserved-word-list.html

Even though you are working with Excel, there could be some background conflict because Excel, Access, and VBA can and do overlap in various ways.

And take a closer look at the named ranges that were created and some of the details that are involved and required.

FYI:

https://support.microsoft.com/en-us...orksheet-fd8905ed-1130-4cca-9bb0-ad02b7e594fd

https://www.ablebits.com/office-addins-blog/excel-named-range/

 

[dradenpoel]

...

In Dutch we would refer to what you do as "muggenziften". Does not really add something.
There is a very clear reference of which range is the named range employee, underneath the formula.

I did indeed check the named ranges in excel, even trying with different named ranges and different ranges altogether, all resulting in the formula not working, where it does work in google sheets.

My conclusion is the not working does not relate to the named ranges.
Might still be that I oversee something though, human eyes.

 

[dradenpoel]

...

I did another check, one of my named ranges was one row shorter then the other ones.
Adapted it to be the same as the other ranges and works now.

Thank you for pointing my attention to the named ranges again.

 

[Ralston18]