Index/Match with multiple possible answers?

问题:

There's a question about how to return some info in Excel, COUNTIFS for VLOOKUP. from @THATNewbie.

Here's a quick summary:

They have this table:

Report Name        User Name      Report Category
Report 1           John Smith     Sales
Report 1           Jack Black     Sales
Report 1           Connie Rae     Sales
Report 1           Brain Bonds    Sales
Report 2           John Smith     Sales
Report 2           Connie Rae     Sales
Report 3           Jack Black     Inventory

And they would like to return the "Report Name" based on User Name and Report Category.

My first thought was just to use Index/Match (as an Array)...however, I realize that if I use "John Smith" and "Sales" to look up the Report Name, there's two possible outcomes: Report 1 and Report 2. Index/Match will always return Report1 (or whatever comes first, going down that column).

My question is: Is there a way to write the Index/Match formula to check if it's already found Report1 and therefore to go to the next match (Report2)?

Here's a screenshot to help visualize. As you can see, the Index/Match correctly finds Report1 in C12, but also in C13. Can you have the formula "look above" and if it's the answer that it WOULD return, to skip that and look for the next? Does that make sense?

回答1:

You can try something like this:

=INDEX(Report_Name,MATCH(The_User&":"&The_Category,User_Name&":"&Report_Category,0))

The idea is to concatenate user name and report category into a single search item. (I added a colon char as a delimiter; this was optional and could possibly be omitted.) Then use MATCH to get the index of the matching item, and INDEX to convert the index to a specific report.

Hope that helps.

回答2:

Unfortunately there is no way (to my knowledge) to do this. You will have to add some sort of unique identifier to each row, or a value that helps define it uniquely. For example, you could add a new column with this function

=COUNTIFS($B$2:$B2, "=" & $B2, $C$2:$C2, "=" & $C2)

What this will do is count the total number times that that specific grouping has shown up, and effectively act as a pseudo ID for it. Then you can add that item to your Index/Match

Then in the second table you showed in the image, you just repeat the count function in the match, so you will have

=INDEX($A$2:$A$8, MATCH(1, (A12 = $B$2:$B$8) * (B12 = $C$2:$C$8) * (COUNTIFS($A$12:$A12, "=" & $A2, $B$2:$B2, "=" & $B2) = $D$2:$D8), 0))

This is an array entered forumla