Excel - formula for matrix (index match)
Excel - formula for matrix (index match)
I searched extensively and still couldn't locate the solution. My understanding of the formula is not strong enough.
I am creating a dynamic lookup and merging multiple data columns into a summary based on a week number.
What I have:
The list in AD5 appears in AK6:AK19 using this formula (passed to the next two cells):
Code:
=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 aim to do is also include a remark in column AE based on the names in AD.
I modified the formula as follows (and applied it to the next two cells):
Code:
=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 doesn’t work because the matrices have changed, which is necessary for the formula to function as I thought while researching.
Anyone?
EDIT:
If you can’t see the images, there’s an example file available here:
google sheets example file
Corrected AF to AE reference
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 necessary information is present.
The image should really clarify this point, perhaps my accompanying text isn't enough.
The countif is included in the matrix formula, which explains why the index formula requires multiple arguments to produce the final list of names.
How it functions is still unclear to me, as I believe my understanding of the formula is incomplete.
If there are images they aren't uploaded securely or trusted, many browsers will prevent them. It seems you're trying to figure out whether AE5 or AD5 should be used, and you're unsure about the contents of the B column unless you've set up specific fixed variables.
An example file is available there:
google sheets example file
What I'm attempting now is to also include the remark in col AF, according to the list of names in AD. " [My underline.]" Based on the code I believe you meant AE rather than AF. The remark seems to be in AE but might be in AF as well? = = = = Also: I notice column headers for 'week' and 'team' but not 'employer'. I see a column called 'name' which isn't in the code. Just wondering...
The choice is clear—either use a Name or an Employee. No mixing allowed. Such decisions, even if they don’t seem important, can lead to confusion. Consistent naming ensures smoother reading, troubleshooting, testing, and documentation. Also, remember that certain names are reserved words.