Forum Discussion
Is it possible to get the combinations using some lookup or any other functions
One Master table should look in 3 other tables. When selected thru a drop down it shud bring down the below outputs:
Scen: AC //Should be dropdown
Month: Jan //Should be dropdown
Org: Org1 //Should be dropdown
Should return concatenation = $AB$1
Scen:AC
Month:Feb
Org: Org1
Should return = $AB$1:$AC$1
| Qtr1 | Qtr1 | Qtr1 | Qtr2 | Qtr2 | Qtr2 | Qtr3 | Qtr3 | Qtr3 | Qtr4 | Qtr4 | Qtr4 | |
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
| AC | $AB | $AC | $AD | $AE | $AF | $AG | $AH | $AI | $AJ | $AK | $AL | $AM |
| CV | $AA | $BB | $CC | $DD | $EE | $FF | $GG | $HH | $II | $JJ | $KK | $LL |
| PY | $A | $B | $X | $Y | $Z | $N | $M | $L | $O | $P | $Q | $R |
| AC | Org1 | 1 | CV | Org1 | 4 | PY | Org1 | 7 | ||||
| AC | Org2 | 2 | CV | Org2 | 5 | PY | Org2 | 8 | ||||
| AC | Org3 | 3 | CV | Org3 | 6 | PY | Org3 | 9 | ||||
| Ouput1 | AC | Jan | Org1 | $AB$1 | ||||||||
| AC | Feb | Org1 | $AB$1:$AC$1 | |||||||||
| AC | Mar | Org1 | $AB$1:$AC$1:$AD$1 | |||||||||
| Output2 | ||||||||||||
| CV | Jan | Org1 | $AA$4 | |||||||||
| CV | Feb | Org1 | $AA$4:$BB4 | |||||||||
| CV | Mar | Org1 | $AA$4:$BB$4:$CC$4 |
If you are on Office365 subscription you may use TEXTJOIN. For the first of highlighted cells
the array (Ctrl+Shift+Enter) formula could be
=TEXTJOIN(":",TRUE,OFFSET($B$4,MATCH(B15,$A$5:$A$7,0),0,,MATCH(C15,$B$4:$F$4,0))&"$"&INDEX($C$10:$C$12,MATCH(D15,$B$10:$B$12,0)))Please see attached.
