Index/Match with multiple possible answers?

Question

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?

Can you add your current function that always gives the first — Holmes IV
I can show you the steps I use to solve this if you like; however, it doesn't include Index/Match. — rwilson
@rwilson - I see your solution in the other thread, thanks for that! — BruceWayne
Yeah, that was one nasty formula due to how the table was shaped and the desired output. This question you have here is a more reasonable one though and is a situation that I see come up often when buidlng array formulas. — rwilson

xidgel xidgel · Accepted Answer · 2015-08-06T13:50:55

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.

Index/Match with multiple possible answers?

3 Answers