索引/匹配多个可能的答案？ [英] Index/Match with multiple possible answers?

问题描述

有关如何在Excel中返回某些信息的问题，请针对VLOOKUP的COUNTIFS 。来自@THATNewbie。

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

以下是快速汇总：

他们有这个表：

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.

我的第一个想法只是使用Index / Match（作为一个数组）...但是，我意识到，如果我使用John Smith和Sales查询报告名称，有两个可能的结果：报告1 和报告2 。索引/匹配将始终返回 Report1 （或者先于此列中的任何内容）。

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).

我的问题是：有没有办法编写索引/匹配公式来检查是否已经找到 Report1 ，因此进入下一个匹配（ Report2 ）？

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)?

以下是屏幕截图以帮助可视化。您可以看到，索引/匹配在C12中正确找到 Report1 ，而在C13中。你可以使用公式看上去，如果它是应该返回的答案，跳过，寻找下一个？这样做是有道理的吗？

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?

推荐答案

你可以尝试这样：

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

想法是将用户名和报告类别连接成单个搜索项。 （我添加一个冒号字符作为分隔符;这是可选的，可以省略）然后使用MATCH获取匹配项的索引，并将INDEX转换为特定报表。

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.

希望有所帮助。

这篇关于索引/匹配多个可能的答案？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！