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]

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)

Hmm, not sure why you would want to use the more complicated one?
But anyway, try this

=INDEX(C1:J40,MATCH(INDEX(L3:L4,L5)&" "&INDEX(M3:M4,M5),B1:B39,0)+2+N9,MATCH(L13,C1:J1,0)-1+O7)

 

[ayazgreat]

Thank you very much peter, so kind of you