CODEWITHSUNDEEP
excelexcel-formula
osomanden
osomanden

Reputation: 611

Get cell value based on current month

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

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

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.

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

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

Upvotes: 0

Views: 1684

Answers (2)

eirikdaude
eirikdaude

Reputation: 3254

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

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.

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.

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.

enter image description here

Upvotes: 1

osomanden
osomanden

Reputation: 611

I just had a simple solution:

=IF($H$3="c";H6;IF($I$3="c";I6;IF($J$3="c";J6;IF($K$3="c";K6;IF($L$3="c";L6;IF($M$3="c";M6;IF($N$3="c";N6;IF($O$3="c";O6;IF($P$3="c";P6;IF($Q$3="c";Q6;IF($R$3="c";R6;IF($S$3="c";S6;"None"))))))))))))

A long and not so smart solution.. but still.. a solution ;)

Upvotes: 0

Related Questions