Xlookup or Match and Index

[neasds]

I need to do a Vlookup in a column to get results on year and camp number in the following table: Suggestions?

Year	Region	Area	Camp_Number	Camp_Name
2010	Northeast	25	U32003	Ahoskie
2011	Northeast	25	U32003	Ahoskie
2012	Northeast	25	U32003	Ahoskie
2013	Northeast	25	U32003	Ahoskie
2014	Northeast	25	U32003	Ahoskie
2015	Northeast	25	U32003	Ahoskie
2016	Northeast	25	U32003	Ahoskie
2017	Northeast	25	U32003	Ahoskie
2018	Northeast	25	U32003	Ahoskie
2019	Northeast	25	U32003	Ahoskie
2020	Northeast	25	U32003	Ahoskie
2010	North Central	13	U32001	Alamance East
2011	North Central	13	U32001	Alamance East
2012	North Central	13	U32001	Alamance East
2013	North Central	13	U32001	Alamance East

 

[Fluff]

You haven't said what you want returned, but how about.

+Fluff New.xlsm
	A	B	C	D	E	F	G	H
1	Year	Region	Area	Camp_Number	Camp_Name
2	2010	Northeast	25	U32003	Ahoskie		Year	2012
3	2011	Northeast	25	U32003	Ahoskie		Camp	U32001
4	2012	Northeast	25	U32003	Ahoskie		Result	Alamance East
5	2013	Northeast	25	U32003	Ahoskie
6	2014	Northeast	25	U32003	Ahoskie
7	2015	Northeast	25	U32003	Ahoskie
8	2016	Northeast	25	U32003	Ahoskie
9	2017	Northeast	25	U32003	Ahoskie
10	2018	Northeast	25	U32003	Ahoskie
11	2019	Northeast	25	U32003	Ahoskie
12	2020	Northeast	25	U32003	Ahoskie
13	2010	North Central	13	U32001	Alamance East
14	2011	North Central	13	U32001	Alamance East
15	2012	North Central	13	U32001	Alamance East
16	2013	North Central	13	U32001	Alamance East
Main

Cell Formulas
Range	Formula
H4	H4	=XLOOKUP(H2&"|"&H3,A2:A16&"|"&D2:D16,E2:E16)

 

[neasds]

Thank you so much Fluff.

To be explicit, I am looking for items in another workbook that matches the year and camp name and inserting them in column f.

 

[Fluff]

Can you please use the XL2BB add-in to post some sample data from both books, along with expected results?

 

[neasds]

Forgive me for my ignorance but is this what you asked for?

History of Placements.xlsx
	L
9
SSP1132_201105

Historical Results.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Year	Region	Area	Camp_Number	Camp_Name	Scripture_Funds_Goal	Scripture_Receipts	Percentage_Goal	Church	GideonCard	Faith_Fund	Designated_Camp	Designated_Service	Auxiliary_Scripture	Scripture_Fund_Balance	Presentations	Donors	Gideons	Auxiliary	Gid_PWT	AUX_PWT
2	2010	Northeast	25	U32003	Ahoskie	$ 22,660	$ 27,073	119.47	$ 15,486	$ 5,728	$ 5,249	$ -	$ -	$ 610		43	143	39	16
3	2011	Northeast	25	U32003	Ahoskie	$ 28,470	$ 18,924	66.47	$ 8,878	$ 3,575	$ 5,812	$ -	$ -	$ 659		23	143	35	15
4	2012	Northeast	25	U32003	Ahoskie	$ 19,510	$ 19,197	98.4	$ 7,324	$ 4,360	$ 6,834	$ -	$ -	$ 680		25	126	33	15
5	2013	Northeast	25	U32003	Ahoskie	$ 19,740	$ 29,588	149.89	$ 16,932	$ 7,555	$ 4,424	$ -	$ -	$ 678		35	179	29	12
6	2014	Northeast	25	U32003	Ahoskie	$ 31,630	$ 20,171	63.77	$ 9,634	$ 4,247	$ 5,037	$ 625		$ 628		22	105	30	13
7	2015	Northeast	25	U32003	Ahoskie	$ 21,680	$ 17,429	80.39	$ 7,284	$ 5,285	$ 4,358	$ -	$ -	$ 503		22	110	27	11
8	2016	Northeast	25	U32003	Ahoskie	19300	14031	72.7	4413	3275	5613	240	0	490		12	81	28	11
9	2017	Northeast	25	U32003	Ahoskie	$ 15,985	$ 18,137	113.46	$ 7,285	$ 4,870	$ 5,487	$ -		$ 495		18		26	10
Camps

 

[Fluff]

Thanks for that, but I also need to know what the other workbook looks like & what results you want.

 

[neasds]

History of Placements.xlsx
	A	B	C	D	E	F	G
1	Year	Region	Area	Camp_Number	Camp_Name	G_PWT	Aux_PWT
2	2011	Northeast	25	U32003	Ahoskie	302	100
3	2011	North Central	13	U32001	Alamance East	3820	1000
4	2011	North Central	13	U32002	Alamance West	350	0
5	2011	North Central	09	U32006	Alleghany County	147	290
6	2011	South Central	17	U32004	Anson County	109	100
7	2011	North Central	12	U32007	Archdale	50	100
8	2011	West	05	U32172	Ashe North	535	100
9	2011	West	05	U32087	Ashe South	506	250
10	2011	Far West	02	U32010	Asheville	717	25
11	2011	Far West	04	U32013	Avery County	200	50
12	2011	Southeast	19	U32017	Bladen County	51	50
13	2011	West	05	U32020	Boone	207	300
14	2011	Far West	03	U32022	Brevard	1150	0
15	2011	Southeast	19	U32024	Brunswick County	665	100
16	2011	Far West	02	U32026	Buncombe East	0	0
17	2011	Far West	02	U32011	Buncombe North	1000	200
18	2011	Far West	02	U32025	Buncombe West	3900	0
19	2011	Far West	04	U32028	Burke East	300	50
20	2011	Far West	04	U32005	Burke North	400	100
21	2011	Far West	04	U32027	Burke West	156	100
22	2011	North Central	13	U32030	Burlington	100	0
23	2011	South Central	16	U32031	Cabarrus East	625	150
24	2011	South Central	16	U32169	Cabarrus Northwest	200	0
25	2011	South Central	16	U32038	Cabarrus South	400	400
26	2011	South Central	16	U32032	Cabarrus West	650	200
27	2011	West	05	U32033	Caldwell South	501	150
28	2011	North Central	11	U32034	Caswell County	601	100
29	2011	West	07	U32035	Catawba East	700	0
30	2011	West	07	U32018	Catawba West	0	0
31	2011	West	08	U32008	Charlotte East	475	0
32	2011	West	08	U32040	Charlotte North	1363	100
33	2011	West	08	U32039	Charlotte Northeast	110	0
34	2011	West	08	U32009	Charlotte South	221	170
35	2011	West	08	U32042	Charlotte Southeast	301	215
36	2011	West	08	U32043	Charlotte Southwest	418	0
37	2011	West	08	U32041	Charlotte West	401	0
SSP1132_201105

I want to lookup up the G_PWT and Aux_PWT amounts and place them in the correct years and camps.

 

[Fluff]

Ok, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Year	Region	Area	Camp_Number	Camp_Name	Scripture_Funds_Goal	Scripture_Receipts	Percentage_Goal	Church	GideonCard	Faith_Fund	Designated_Camp	Designated_Service	Auxiliary_Scripture	Scripture_Fund_Balance	Presentations	Donors	Gideons	Auxiliary	Gid_PWT	AUX_PWT
2	2010	Northeast	25	U32003	Ahoskie	22660	27073	119.47	15486	5728	5249	0	0	610		43	143	39	16
3	2011	Northeast	25	U32003	Ahoskie	28470	18924	66.47	8878	3575	5812	0	0	659		23	143	35	15	302	100
4	2012	Northeast	25	U32003	Ahoskie	19510	19197	98.4	7324	4360	6834	0	0	680		25	126	33	15
5	2013	Northeast	25	U32003	Ahoskie	19740	29588	149.89	16932	7555	4424	0	0	678		35	179	29	12
6	2014	Northeast	25	U32003	Ahoskie	31630	20171	63.77	9634	4247	5037	625		628		22	105	30	13
7	2015	Northeast	25	U32003	Ahoskie	21680	17429	80.39	7284	5285	4358	0	0	503		22	110	27	11
8	2016	Northeast	25	U32003	Ahoskie	19300	14031	72.7	4413	3275	5613	240	0	490		12	81	28	11
9	2017	Northeast	25	U32003	Ahoskie	15985	18137	113.46	7285	4870	5487	0		495		18		26	10
Camps

Cell Formulas
Range	Formula
T2:T9	T2	=XLOOKUP($A2&"|"&$D2,SSP1132_201105!$A$2:$A$100&"|"&SSP1132_201105!$D$2:$D$100,SSP1132_201105!F$2:F$100,"",0)
U2:U9	U2	=XLOOKUP($A2&"|"&$D2,SSP1132_201105!$A$2:$A$37&"|"&SSP1132_201105!$D$2:$D$37,SSP1132_201105!G$2:G$37,"",0)

 

[Fluff]

Another option, if the year & camp only occur once, would be

=SUMIFS(SSP1132_201105!F:F,SSP1132_201105!$A:$A,$A2,SSP1132_201105!$D:$D,$D2)

 

[neasds]

Fluff, it worked great. Thank you so much for your help.