根据当前月份获取单元格值 [英] Get cell value based on current month

问题描述

我正在尝试根据其他单元格值设置当前行/单元格的值.

I am trying to set value for current row/cell based on other cell value.

带有"c"的行设置为当前月份(=IF(d1=TEXT(TODAY();"MMM");"c";""))

Row with "c" is set as current month (=IF(d1=TEXT(TODAY();"MMM");"c";""))

    A   B   C   D   E   F   G
1               c
2      JAN FEB MAR APR MAY
3       32  34  23  22  44
4  33   22  25  33  44  22
5       12  12  33  32  32

因此，在(每行)当前月份"下方我想在单元格中获得"c"对应的值.位于第一行.

So, just below (per row) "Current Month" I would like to get the corresponding value in the cell where "c" is present in top row.

在图像示例中，因为"c"在D列中，设置的数字为33(D列中的值，第4行)

In image example, because the "c" is in D column, the number set is 33(value in D column, row 4)

任何人-单元格"33"中的公式将如何显示?是吗?

Anyone - how would the formula in the cell "33" be?

推荐答案

假设您的月份名称采用与TEXT(TODAY();"MMM")相同的格式，则可以输入类似=XLOOKUP(TEXT(TODAY();"MMM");$B$3:$M$3;$B4:$M4;"no match")的内容.

Assuming your monthnames are in a format similar to what TEXT(TODAY();"MMM") will give, you can put in something like =XLOOKUP(TEXT(TODAY();"MMM");$B$3:$M$3;$B4:$M4;"no match").

如果您查看 XLOOKUP的文档，您将看到第一个参数是要搜索的文本，第二个参数是要搜索的范围，第三个参数是要从中返回结果的范围.第四个参数不是严格必需的，但是如果没有匹配项，则为返回值.

If you look at XLOOKUP's documentation, you'll see that the first argument is the text to search for, the second the range to search, and the third the range to return a result from. The fourth argument is not strictly necessary, but is the return value if there is no match.

在下面的屏幕截图中，我将=TEXT(TODAY();"mmm")放在A3中，将公式=XLOOKUP($A$3;$B$3:$M$3;$B4:$M4;"no match")放在单元格A4中，然后将其复制下来.

In the screenshot below, I've put =TEXT(TODAY();"mmm") in A3, and the formula =XLOOKUP($A$3;$B$3:$M$3;$B4:$M4;"no match") in the cell A4, and just copied it down.

我将=TEXT(TODAY();"mmm")放在其自己的单元格中的原因是，它是

The reason I put =TEXT(TODAY();"mmm") in a cell of its own, is that it's a volatile function, so I don't want it to appear in too many formulas.

这篇关于根据当前月份获取单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！