根据表中的Month标题查找单元格数据 [英] Look up Cell data based on Month header in table

问题描述

我正在尝试创建一个用于数据验证的小型电子表格。
下面我创建了两个表，表1和表2。

i'm trying to create a small spreadsheet for data verification. Below i have created two tables Table 1 and Table 2.

我正在寻找的是一个简单的=索引匹配或类似的检查当前月然后将该值复制到相应的组行验证。

What i am trying to find is a simple =Index Match or similar to check for Current month then copy that value to respective Group row under verification.

问题是这几个月是一个标题行，我不知道如何检查标题行正确的一个月将其中的单元格中的值输出到输出列表中的每个对应组。

Problem is that the months are a header row and i have no idea how to check a header row for correct month then output the values in the cells below it to each corresponding group in the output list.

所以把它放在编程术语中，这就是我想要的东西

So to put it in programming terms this is what im looking for

If A2=Table2[Header] then c8=C4 else table[header] + step right

A2包含今天Date（= today（）），标题的缩写月份名称为Jan Feb Mar等等。
如上所述，我不知道我是否对我来说更难，所以真的应该是。我正在使用Excel 2013。

A2 contains todays Date (=today()) and Header has the shorten Month names as Jan Feb Mar and so on. Something similar as above, i don't know if i'm making it harder for me then it really should be. I'm working with Excel 2013.

Output list
Group    Input  Verification  Differance
G1       120    123           =Sum([@[Input]]-[@[Verification])
G2        76    110           =Sum([@[Input]]-[@[Verification])
G3       110    90            =Sum([@[Input]]-[@[Verification])
G4        34    53            =Sum([@[Input]]-[@[Verification])

Data list
Group    Year   Jan Feb Mar Apr 
G1       2017       123 95  80                              
G2       2017       110 85  75                              
G3       2017       90  80  70                              
G4       2017       53  53  46      

Regards
Johan

Regards Johan

推荐答案

您将使用第二个匹配来找到正确的列：

You would use a second match to find the correct column:

=INDEX(DataList,MATCH([@Group],DataList[Group],0),MATCH(TEXT($A$1,"mmm"),DataList[#Headers],0))

这篇关于根据表中的Month标题查找单元格数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！