Formula needed

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,076
Office Version
  1. 2016
Platform
  1. Windows
Hello All,
I have this data set in a sheet. In cell p4 i have it to take month name like Jan , Feb etc. Then in Q4, numbers such as 1, 2, 3 etc.

What i wanna do is that i need a formula in Q6 that should look at the data in P4 and Q5 then get the result from the table below;
Thanks
Kelly
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
1 A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1
2 A2 B2 C2 D2 E2 F2 G2 H2 I2 J2 K2 L2
3 A3 B3 C3 D3 E3 F3 G3 H3 I3 J3 K3 L3
4 A4 B4 C4 D4 E4 F4 G4 H4 I4 J4 K4 L4
5 A5 B5 C5 D5 E5 F5 G5 H5 I5 J5 K5 L5
6 A6 B6 C6 D6 E6 F6 G6 H6 I6 J6 K6 L6
7 A7 B7 C7 D7 E7 F7 G7 H7 I7 J7 K7 L7
8 A8 B8 C8 D8 E8 F8 G8 H8 I8 J8 K8 L8
9 A9 B9 C9 D9 E9 F9 G9 H9 I9 J9 K9 L9
10 A10 B10 C10 D10 E10 F10 G10 H10 I10 J10 K10 L10
11 A11 B11 C11 D11 E11 F11 G11 H11 I11 J11 K11 L11
12 A12 B12 C12 D12 E12 F12 G12 H12 I12 J12 K12 L12
13 A13 B13 C13 D13 E13 F13 G13 H13 I13 J13 K13 L13
14 A14 B14 C14 D14 E14 F14 G14 H14 I14 J14 K14 L14
15 A15 B15 C15 D15 E15 F15 G15 H15 I15 J15 K15 L15
16 A16 B16 C16 D16 E16 F16 G16 H16 I16 J16 K16 L16
17 A17 B17 C17 D17 E17 F17 G17 H17 I17 J17 K17 L17
18 A18 B18 C18 D18 E18 F18 G18 H18 I18 J18 K18 L18
19 A19 B19 C19 D19 E19 F19 G19 H19 I19 J19 K19 L19
20 A20 B20 C20 D20 E20 F20 G20 H20 I20 J20 K20 L20
21 A21 B21 C21 D21 E21 F21 G21 H21 I21 J21 K21 L21
22 A22 B22 C22 D22 E22 F22 G22 H22 I22 J22 K22 L22
23 A23 B23 C23 D23 E23 F23 G23 H23 I23 J23 K23 L23
24 A24 B24 C24 D24 E24 F24 G24 H24 I24 J24 K24 L24
25 A25 B25 C25 D25 E25 F25 G25 H25 I25 J25 K25 L25
26 A26 B26 C26 D26 E26 F26 G26 H26 I26 J26 K26 L26
27 A27 B27 C27 D27 E27 F27 G27 H27 I27 J27 K27 L27
28 A28 B28 C28 D28 E28 F28 G28 H28 I28 J28 K28 L28
29 A29 B29 C29 D29 E29 F29 G29 H29 I29 J29 K29 L29
30 A30 B30 C30 D30 E30 F30 G30 H30 I30 J30 K30 L30
31 A31 B31 C31 D31 E31 F31 G31 H31 I31 J31 K31 L31
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,076
Office Version
  1. 2016
Platform
  1. Windows
=index(b3:n34,match(q4,b3:b34,0),match(p4,b3:n3,0))

The above is what i used because my table starts from b3. And the values in my table are enter manually
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,299
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
how about

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">JAN</td><td style=";">FEB</td><td style=";">MAR</td><td style=";">APR</td><td style=";">MAY</td><td style=";">JUN</td><td style=";">JUL</td><td style=";">AUG</td><td style=";">SEP</td><td style=";">OCT</td><td style=";">NOV</td><td style=";">DEC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style=";">A1</td><td style=";">B1</td><td style=";">C1</td><td style=";">D1</td><td style=";">E1</td><td style=";">F1</td><td style=";">G1</td><td style=";">H1</td><td style=";">I1</td><td style=";">J1</td><td style=";">K1</td><td style=";">L1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2</td><td style=";">A2</td><td style=";">B2</td><td style=";">C2</td><td style=";">D2</td><td style=";">E2</td><td style=";">F2</td><td style=";">G2</td><td style=";">H2</td><td style=";">I2</td><td style=";">J2</td><td style=";">K2</td><td style=";">L2</td><td style="text-align: right;;"></td><td style="background-color: #E2EFDA;;">JUL</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style=";">A3</td><td style=";">B3</td><td style=";">C3</td><td style=";">D3</td><td style=";">E3</td><td style=";">F3</td><td style=";">G3</td><td style=";">H3</td><td style=";">I3</td><td style=";">J3</td><td style=";">K3</td><td style=";">L3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4</td><td style=";">A4</td><td style=";">B4</td><td style=";">C4</td><td style=";">D4</td><td style=";">E4</td><td style=";">F4</td><td style=";">G4</td><td style=";">H4</td><td style=";">I4</td><td style=";">J4</td><td style=";">K4</td><td style=";">L4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">G4</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5</td><td style=";">A5</td><td style=";">B5</td><td style=";">C5</td><td style=";">D5</td><td style=";">E5</td><td style=";">F5</td><td style=";">G5</td><td style=";">H5</td><td style=";">I5</td><td style=";">J5</td><td style=";">K5</td><td style=";">L5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">6</td><td style=";">A6</td><td style=";">B6</td><td style=";">C6</td><td style=";">D6</td><td style=";">E6</td><td style=";">F6</td><td style=";">G6</td><td style=";">H6</td><td style=";">I6</td><td style=";">J6</td><td style=";">K6</td><td style=";">L6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">7</td><td style=";">A7</td><td style=";">B7</td><td style=";">C7</td><td style=";">D7</td><td style=";">E7</td><td style=";">F7</td><td style=";">G7</td><td style=";">H7</td><td style=";">I7</td><td style=";">J7</td><td style=";">K7</td><td style=";">L7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">8</td><td style=";">A8</td><td style=";">B8</td><td style=";">C8</td><td style=";">D8</td><td style=";">E8</td><td style=";">F8</td><td style=";">G8</td><td style=";">H8</td><td style=";">I8</td><td style=";">J8</td><td style=";">K8</td><td style=";">L8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">9</td><td style=";">A9</td><td style=";">B9</td><td style=";">C9</td><td style=";">D9</td><td style=";">E9</td><td style=";">F9</td><td style=";">G9</td><td style=";">H9</td><td style=";">I9</td><td style=";">J9</td><td style=";">K9</td><td style=";">L9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">10</td><td style=";">A10</td><td style=";">B10</td><td style=";">C10</td><td style=";">D10</td><td style=";">E10</td><td style=";">F10</td><td style=";">G10</td><td style=";">H10</td><td style=";">I10</td><td style=";">J10</td><td style=";">K10</td><td style=";">L10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">11</td><td style=";">A11</td><td style=";">B11</td><td style=";">C11</td><td style=";">D11</td><td style=";">E11</td><td style=";">F11</td><td style=";">G11</td><td style=";">H11</td><td style=";">I11</td><td style=";">J11</td><td style=";">K11</td><td style=";">L11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">12</td><td style=";">A12</td><td style=";">B12</td><td style=";">C12</td><td style=";">D12</td><td style=";">E12</td><td style=";">F12</td><td style=";">G12</td><td style=";">H12</td><td style=";">I12</td><td style=";">J12</td><td style=";">K12</td><td style=";">L12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">13</td><td style=";">A13</td><td style=";">B13</td><td style=";">C13</td><td style=";">D13</td><td style=";">E13</td><td style=";">F13</td><td style=";">G13</td><td style=";">H13</td><td style=";">I13</td><td style=";">J13</td><td style=";">K13</td><td style=";">L13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">14</td><td style=";">A14</td><td style=";">B14</td><td style=";">C14</td><td style=";">D14</td><td style=";">E14</td><td style=";">F14</td><td style=";">G14</td><td style=";">H14</td><td style=";">I14</td><td style=";">J14</td><td style=";">K14</td><td style=";">L14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">15</td><td style=";">A15</td><td style=";">B15</td><td style=";">C15</td><td style=";">D15</td><td style=";">E15</td><td style=";">F15</td><td style=";">G15</td><td style=";">H15</td><td style=";">I15</td><td style=";">J15</td><td style=";">K15</td><td style=";">L15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">16</td><td style=";">A16</td><td style=";">B16</td><td style=";">C16</td><td style=";">D16</td><td style=";">E16</td><td style=";">F16</td><td style=";">G16</td><td style=";">H16</td><td style=";">I16</td><td style=";">J16</td><td style=";">K16</td><td style=";">L16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">17</td><td style=";">A17</td><td style=";">B17</td><td style=";">C17</td><td style=";">D17</td><td style=";">E17</td><td style=";">F17</td><td style=";">G17</td><td style=";">H17</td><td style=";">I17</td><td style=";">J17</td><td style=";">K17</td><td style=";">L17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">18</td><td style=";">A18</td><td style=";">B18</td><td style=";">C18</td><td style=";">D18</td><td style=";">E18</td><td style=";">F18</td><td style=";">G18</td><td style=";">H18</td><td style=";">I18</td><td style=";">J18</td><td style=";">K18</td><td style=";">L18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">19</td><td style=";">A19</td><td style=";">B19</td><td style=";">C19</td><td style=";">D19</td><td style=";">E19</td><td style=";">F19</td><td style=";">G19</td><td style=";">H19</td><td style=";">I19</td><td style=";">J19</td><td style=";">K19</td><td style=";">L19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">20</td><td style=";">A20</td><td style=";">B20</td><td style=";">C20</td><td style=";">D20</td><td style=";">E20</td><td style=";">F20</td><td style=";">G20</td><td style=";">H20</td><td style=";">I20</td><td style=";">J20</td><td style=";">K20</td><td style=";">L20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">21</td><td style=";">A21</td><td style=";">B21</td><td style=";">C21</td><td style=";">D21</td><td style=";">E21</td><td style=";">F21</td><td style=";">G21</td><td style=";">H21</td><td style=";">I21</td><td style=";">J21</td><td style=";">K21</td><td style=";">L21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">22</td><td style=";">A22</td><td style=";">B22</td><td style=";">C22</td><td style=";">D22</td><td style=";">E22</td><td style=";">F22</td><td style=";">G22</td><td style=";">H22</td><td style=";">I22</td><td style=";">J22</td><td style=";">K22</td><td style=";">L22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">23</td><td style=";">A23</td><td style=";">B23</td><td style=";">C23</td><td style=";">D23</td><td style=";">E23</td><td style=";">F23</td><td style=";">G23</td><td style=";">H23</td><td style=";">I23</td><td style=";">J23</td><td style=";">K23</td><td style=";">L23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">24</td><td style=";">A24</td><td style=";">B24</td><td style=";">C24</td><td style=";">D24</td><td style=";">E24</td><td style=";">F24</td><td style=";">G24</td><td style=";">H24</td><td style=";">I24</td><td style=";">J24</td><td style=";">K24</td><td style=";">L24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">25</td><td style=";">A25</td><td style=";">B25</td><td style=";">C25</td><td style=";">D25</td><td style=";">E25</td><td style=";">F25</td><td style=";">G25</td><td style=";">H25</td><td style=";">I25</td><td style=";">J25</td><td style=";">K25</td><td style=";">L25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">26</td><td style=";">A26</td><td style=";">B26</td><td style=";">C26</td><td style=";">D26</td><td style=";">E26</td><td style=";">F26</td><td style=";">G26</td><td style=";">H26</td><td style=";">I26</td><td style=";">J26</td><td style=";">K26</td><td style=";">L26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">27</td><td style=";">A27</td><td style=";">B27</td><td style=";">C27</td><td style=";">D27</td><td style=";">E27</td><td style=";">F27</td><td style=";">G27</td><td style=";">H27</td><td style=";">I27</td><td style=";">J27</td><td style=";">K27</td><td style=";">L27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">28</td><td style=";">A28</td><td style=";">B28</td><td style=";">C28</td><td style=";">D28</td><td style=";">E28</td><td style=";">F28</td><td style=";">G28</td><td style=";">H28</td><td style=";">I28</td><td style=";">J28</td><td style=";">K28</td><td style=";">L28</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">29</td><td style=";">A29</td><td style=";">B29</td><td style=";">C29</td><td style=";">D29</td><td style=";">E29</td><td style=";">F29</td><td style=";">G29</td><td style=";">H29</td><td style=";">I29</td><td style=";">J29</td><td style=";">K29</td><td style=";">L29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">30</td><td style=";">A30</td><td style=";">B30</td><td style=";">C30</td><td style=";">D30</td><td style=";">E30</td><td style=";">F30</td><td style=";">G30</td><td style=";">H30</td><td style=";">I30</td><td style=";">J30</td><td style=";">K30</td><td style=";">L30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">31</td><td style=";">A31</td><td style=";">B31</td><td style=";">C31</td><td style=";">D31</td><td style=";">E31</td><td style=";">F31</td><td style=";">G31</td><td style=";">H31</td><td style=";">I31</td><td style=";">J31</td><td style=";">K31</td><td style=";">L31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Q6</th><td style="text-align:left">=INDEX(<font color="Blue">$C$3:$N$33,MATCH(<font color="Red">Q5,$B$3:$B$33,0</font>),MATCH(<font color="Red">P4,$C$2:$N$2,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,076
Office Version
  1. 2016
Platform
  1. Windows
Q6 =INDEX($C$3:$N$33,MATCH(Q5,$B$3:$B$33,0),MATCH(P4,$C$2:$N$2,0))


Thanks AlanY for this. You have made it again. Thanks a lot.
Kelly
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,299
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,694
Members
425,231
Latest member
mramb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top