Formula needed

[kelly mort]

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

 

[peterhinton]

where does the table start ?
you can use INDEX & MATCH for this

 

[BarryL]

what do the numbers in Q4 (Q5?) represent? if they are in column A use the below.


=INDEX(A1:L31,MATCH(Q4,A1:A31,0),MATCH(P4,A1:L1,0))

 

[kelly mort]

where does the table start ?
you can use INDEX & MATCH for this

It starts in B3

 

[peterhinton]

Or maybe i read it wrong,

try
=ADDRESS(Q14+2,IF(P4="JAN",1,IF(P4="FEB",2,IF(P4="MAR",3,IF(P4="APR",4,IF(P4="MAY",5,IF(P4="JUN",6,IF(P4="JUL",7,IF(P4="AUG",8,IF(P4="SEP",9,IF(P4="OCT",10,IF(P4="NOV",11,IF(P4="DEC",12))))))))))))+1)

of

=INDIRECT(ADDRESS(Q14+2,IF(P4="JAN",1,IF(P4="FEB",2,IF(P4="MAR",3,IF(P4="APR",4,IF(P4="MAY",5,IF(P4="JUN",6,IF(P4="JUL",7,IF(P4="AUG",8,IF(P4="SEP",9,IF(P4="OCT",10,IF(P4="NOV",11,IF(P4="DEC",12))))))))))))+1))

 

[peterhinton]

=index(b2:m32,match(q4,b2:b32,0),match(p4,b2:m2,0))

 

[kelly mort]

=index(b2:m32,match(q4,b2:b32,0),match(p4,b2:m2,0))

I get #REF!

 

[kelly mort]

what do the numbers in Q4 (Q5?) represent? if they are in column A use the below.


=INDEX(A1:L31,MATCH(Q4,A1:A31,0),MATCH(P4,A1:L1,0))

I think somethink is missing in this formula because when i hit enter a dialog box pops up saying there is a problem with formula

 

[peterhinton]

index and match will work, just adjust the arrays to suit your table etc.

 

[steve the fish]

I don't think you can get a reference error from that formula unless the result itself points to a cell that contains a reference error.