I have the below table, and have created the Wk2-Wk3 column by using a nested IF/AND function
=
IF(AND(C3="Newbie",D3="Promise"),"N-P",
IF(AND(C3="Newbie",D3="Sleepy"),"N-S",
...
IF(AND(C3="Broken Promise",D3="Newbie"),"Bp-N",
IF(AND(C3="Fallen Star",D3="Newbie"),"Fs-N"
)))))))))))))
| ID | Wk 1 | Wk 2 | Wk 3 | Wk2-Wk3 |
|---|---|---|---|---|
| 1 | Newbie | Promise | Waning Promise | P-Wp |
| 2 | Newbie | Sleepy | Hibernating | S-H |
| 3 | Promise | Promise | Star | P-S |
| 4 | Promise | Waning Promise | Broken Promise | Wp-Bp |
| 5 | Hibernating | Hibernating | Newbie | H-N |
| 6 | Newbie | Newbie | Promise | N-P |
However, as the weeks progress I will be adding additional 'Wk' columns and therefore the last column will be changing to Wk3-Wk4, Wk4-Wk5, .. and so on.
I don't want to have to keep altering cell references. Is there a way to find the last column using a fixed formula that always uses the two latest weeks.
I tried using OFFSET, but cannot find a way to reference in relation to the formula cell. The following would not work:
=
IF(AND(((OFFSET(E2,0,-2))="Newbie",((OFFSET(E2,0,-1))="Promise"),"N-P",
IF(AND(((OFFSET(E2,0,-2))="Newbie",((OFFSET(E2,0,-1))="Sleepy"),"N-S",
...
IF(AND(((OFFSET(E2,0,-2))="Broken Promise",((OFFSET(E2,0,-1))="Newbie"),"Bp-N",
IF(AND(((OFFSET(E2,0,-2))="Fallen Star",((OFFSET(E2,0,-1))="Newbie"),"Fs-N"
)))))))))))))
Using latest version of Excel, thank you in advance
CodePudding user response：
Use a lookup table (Insert > Table from the ribbon, or alternatively create an ordinary range and use cell references instead of the table syntax in the formula below).
| Value | Abbreviation |
|---|---|
| Newbie | N |
| Promise | P |
| Waning promise | Wp |
| ... | ... |
From this, the value in your Wk2-Wk3 column can be calculated with a VLOOKUP(), XLOOKUP() or INDEX(MATCH()). For example,
=VLOOKUP(C3,LookupTable[[Value]:[Abbreviation]],2,FALSE)&"-"&VLOOKUP(D3,LookupTable[[Value]:[Abbreviation]],2,FALSE)
where LookupTable is the name of the table (you can set this on the "Table Design" ribbon tab).
CodePudding user response：
If I understand correctly, then 
In D1 enter formula: =OFFSET(D1,0,-2)
In E1 enter formula: =OFFSET(E1,0,-2)
Note that both formulas refer to themselves. With most functions this would create a 
IF(IFERROR(FIND(" ",C3,1),0),LEFT(C3,1)&LOWER(MID(C3,FIND(" ",C3,1) 1,1)),LEFT(C3,1))&"-"&IF(IFERROR(FIND(" ",D3,1),0),LEFT(D3,1)&LOWER(MID(D3,FIND(" ",D3,1) 1,1)),LEFT(D3,1))
And, if you add the new week data columns appropriately then all you need to do is drag right...