A difficult Lookup

[ayazgreat]

Hi All, I have got below table and I need a formula to search values as per from col h to k, let me explain you in col H row no.4 In = 1 and Out = 2, in col I Request = 1 and Quantity = 2, in col J values are in col A and Col K values are in row 2 from col c to f. what formula should I put in cell I12 to get result by looking up values in h4, i4, j8, and k6 as shown below highlighted result with accurate in values in col I 12. I mean to say here look up each value in its given table like In request table or In Quantity or Out Request or out Quantity Sheet1

	A	B	C	D	E	F	G	H	I	J	K
1		In Request
2			1	2	3	4		In	Request	SD	P
3		Product	P	I	K	M		Out	Quantity	JZ	I
4	1	SD	1000	1584	2236	26		1	2	SS	K
5	2	JZ	94	220	382	27				WM	L
6	3	SS	744	326	928	20				AS	3
7	4	WM	68			7				PS
8	5	AS				1				2
9	6	PS				55
10
11		In Quantity
12			1	2	3	4		Result	71,010
13		Product	P	I	K	M
14	1	SD	2986755	4965095	6244840	15524000
15	2	JZ	25523	38868	71010	133442
16	3	SS	152477	100445	346908	969013
17	4	WM	2104			1206
18	5	AS				5
19	6	PS				514
20
21		Out Request
22			1	2	3	4
23		Product	P	I	K	M
24	1	SD	8	8	8	3
25	2	JZ	9	18	99	12
26	3	SS	13	5	6	12
27	4	WM	12		1	6
28	5	AS				7
29	6	PS				22
30
31		Out Quantity
32			1	2	3	4
33		Product	P	I	K	M
34	1	SD	2021800	4487000	6021800	12110000
35	2	JZ	41173	37035	55152	1396
36	3	SS	260722	108994	552236	53083
37	4	WM	1329		1	1205
38	5	AS				1088
39	6	PS				579

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Assuming "In Request", "In Quantity" etc is actually in the column B cell in that row (and merged or centered across to column F) then try

=INDEX(C1:F39,MATCH(INDEX(H2:H3,H4)&" "&INDEX(I2:I3,I4),B1:B39,0)+2+J8,K6)

 

[ayazgreat]

Assuming "In Request", "In Quantity" etc is actually in the column B cell in that row (and merged or centered across to column F) then try

=INDEX(C1:F39,MATCH(INDEX(H2:H3,H4)&" "&INDEX(I2:I3,I4),B1:B39,0)+2+J8,K6)

Peter I don't have words to say but thank you very much, you are so intelligent and expert.

 

[Peter_SSs]

Actually, given the uniform spacing of your tables, I think this should work too.

=INDEX(C4:F39,(H4-1)*20+(I4-1)*10+J8,K6)

 

[ayazgreat]

Actually, given the uniform spacing of your tables, I think this should work too.

=INDEX(C4:F39,(H4-1)*20+(I4-1)*10+J8,K6)

Great, it also works but I don't understand *20 and *10

 

[Peter_SSs]

Peter I don't have words to say but thank you very much, you are so intelligent and expert.

Thank you for those very kind words.

Great, it also works but I don't understand *20 and *10

The 20 is because the "Request" tables are exactly 20 rows apart.
The 10 is because each "Quantity" table is exactly 10 rows below the corresponding "Request" table.

 

[ayazgreat]

Thanks for valuable knowledge

 

[ayazgreat]

Dear Peter I have made some changes data and added months in first row so now criteria is to match month also in your formula with same others conditions I would like you to please change in your first provided formula.

	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Jan-13	Feb-13
2	In Request
3		1	2	3	4	1	2	3	4		In	Request	SD	P
4	Product	P	I	K	M	P	I	K	M		Out	Quantity	JZ	I
5	SD	1000	1584	2236	26	10	5	9	2		1	2	SS	K
6	JZ	94	220	382	27	41	38	62	19				WM	L
7	SS	744	326	928	20	6	1	8	20				AS	3
8	WM	68	0	0	7	11	0	0	10				PS
9	AS	0	0	0	1	0	0	0	11				2
10	PS	0	0	0	55	0	0	0	25
11
12	In Quantity
13		1	2	3	4	1	2	3	4		Feb-13	106670
14	Product	P	I	K	M	P	I	K	M
15	SD	2986755	4965095	6244840	15524000	2036720	4353000	5357900	4296800
16	JZ	25523	38868	71010	133442	73634	38089	106670	114416
17	SS	152477	100445	346908	969013	210168	116567	453974	232574
18	WM	2104	0	0	1206	1565	0	0	1566
19	AS	0	0	0	5	0	0	0	1043
20	PS	0	0	0	514	0	0	0	1631
21
22	Out Request

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

Assuming those values are real dates and that they are in columns C & G and that the dates are all in the same year, try

=INDEX(C5:J40,(L5-1)*20+(M5-1)*10+N9,(MONTH(L13)-1)*4+O7)

 

[ayazgreat]

Peter I would be very thankful to you if you please changes of the same in below formula also.=INDEX(C1:F39,MATCH(INDEX(H2:H3,H4)&" "&INDEX(I2:I3,I4),B1:B39,0)+2+J8,K6)