Search and paste value from other sheet (combination of vlookup,hlookup,index)

[Forestq]

Hi,

ino sheet1 I have table:

Into sheet2 I have other table:

How to write a function, that will be automaticly taken value from sheet1 and put into col H - Target, to sheet2?

 

[wideboydixon]

Here's what I have in Sheet 1:

Book1
	A	B	C	P	Q	R	S
1	Sales Level 1	Sales Level 2	COL3	Q1	Q2	Q3	Q4
2	EMEAR-REGION	EMEAR-CENTRAL	A	1	16	31	46
3	EMEAR-REGION	EMEAR-CENTRAL	D	2	17	32	47
4	EMEAR-REGION	EMEAR-CENTRAL	B	3	18	33	48
5	EMEAR-REGION	EMEAR-CENTRAL	C	4	19	34	49
6	EMEAR-REGION	EMEAR-CENTRAL	E	5	20	35	50
7	EMEAR-REGION	EMEAR_GERMANY	A	6	21	36	51
8	EMEAR-REGION	EMEAR_GERMANY	D	7	22	37	52
9	EMEAR-REGION	EMEAR_GERMANY	B	8	23	38	53
10	EMEAR-REGION	EMEAR_GERMANY	C	9	24	39	54
11	EMEAR-REGION	EMEAR_GERMANY	E	10	25	40	55
12	EMEAR-REGION	EMEAR_POL	A	11	26	41	56
13	EMEAR-REGION	EMEAR_POL	D	12	27	42	57
14	EMEAR-REGION	EMEAR_POL	B	13	28	43	58
15	EMEAR-REGION	EMEAR_POL	C	14	29	44	59
16	EMEAR-REGION	EMEAR_POL	E	15	30	45	60
Sheet1

And here's my Sheet 2:

Book1
	A	B	C	D	E	F	G	H
1	Year	Time	Quarter	Theatre	Sales Level 1	Sales Level 2	Segment	Target
2	2016	2016Q1	Q1	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	A	1
3	2016	2016Q2	Q2	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	A	16
4	2016	2016Q3	Q3	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	A	31
5	2016	2016Q4	Q4	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	A	46
6	2016	2016Q1	Q1	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	B	3
7	2016	2016Q2	Q2	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	B	18
8	2016	2016Q3	Q3	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	B	33
9	2016	2016Q4	Q4	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	B	48
10	2016	2016Q1	Q1	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	C	4
11	2016	2016Q2	Q2	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	C	19
12	2016	2016Q3	Q3	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	C	34
13	2016	2016Q4	Q4	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	C	49
14	2016	2016Q1	Q1	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	D	2
15	2016	2016Q2	Q2	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	D	17
16	2016	2016Q3	Q3	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	D	32
17	2016	2016Q4	Q4	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	D	47
18	2016	2016Q1	Q1	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	E	5
19	2016	2016Q2	Q2	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	E	20
20	2016	2016Q3	Q3	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	E	35
21	2016	2016Q4	Q4	CENTRAL	EMEAR-REGION	EMEAR-CENTRAL	E	50
Sheet2

Cell Formulas
Range	Formula
H2	{=INDEX(Sheet1!$P:$S,MATCH(1,(Sheet1!A:A=$E2)*(Sheet1!B:B=$F2)*(Sheet1!C:C=$G2),0),MATCH($C2,Sheet1!$P$1:$S$1,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that's somewhere near what you're after.

WBD

 

[SKM0005]

Hi... If anyone needs the solution...

One way is to:
Make index columns as Sheet1:B+C and Sheet2:F+G, to use as the key fields
Use a formula in sheet2:H with if loops on sheet2:C value to check as “Q1”, “Q2”, “Q3” or “Q4”; to “VLOOKUP” on the above index Sheet1:P,Q,R,S value respectively

 

[Forestq]

thank you wideboydixon! just checked and it's working fine.

 

[wideboydixon]

thank you wideboydixon! just checked and it's working fine.

Good to know. Note that the performance won't be fantastic as it's processing all the rows in the sheet. If you know how many rows you have in Sheet1, you could optimize a little bit.

WBD

 

[Forestq]

working slowly, but working, number of rows is always the same. so I will change it, but one time per quater it can working like this