如何使用Excel返回与多个条件匹配的列标题 [英] How do I return the column header matching multiple criteria using Excel

问题描述

我正在尝试使用多个条件查找数据的子集，并返回满足已定义条件的列标题. 每个唯一标识符都已从法官那里获得奖励(表1).我想使用公式(或宏)返回在tab2中获得最高奖项的法官.

I am trying to do a lookup of a subset of data using multiple criteria and returning the column header that meets the defined criteria. Each unique identifier has received an award from a judge (tab1). I would like to use a formula (or a macro) to return who the judge was that awarded the highest award in tab2.

在第二个选项卡上的工作表中，我一直在尝试使用判断"列中的索引和匹配公式来完成此操作.但是，我的公式返回#N/A.我的公式当前已配置: {= INDEX(数据！$ B $ 2:$ D $ 2，MATCH(A3& B3，数据！$ A $ 3:$ A $ 12& Data！$ B $ 3:$ D $ 12,0)))}

In the worksheet on the second tab, I have been trying to use an index and match formula in the "Judge" column to accomplish this. However, my formula returns #N/A. My formula is currently configured: {=INDEX(Data!$B$2:$D$2,MATCH(A3&B3,Data!$A$3:$A$12&Data!$B$3:$D$12,0))}

结果应该是授予唯一标识符(定义为最高级别)的法官(贝茨，公园或双子座).预先感谢.

The result should be the judge (Bates, Parks, or Gemini) that awarded the unique identifier the award level defined (the highest). Thanks in advance.

推荐答案

使用:

=INDEX(DATA!$2:$2,MATCH(B3,INDEX(DATA!A:D,MATCH(A3,DATA!A:A,0),0),0))

这篇关于如何使用Excel返回与多个条件匹配的列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！