Two-dimensional lookup based on multiple criteria

[roberk9]

I need help with entering data from other sheets into a "Summary" sheet based on multiple criteria.

In my example workbook, I have the Summary sheet to compile information from sheets of data for each year.
Column headers: Fruit type-YR (ex: Cherry-22)
Row header #1: Location (ex: Sacramento)
Row header #2: Person (ex: Nancy)
For example: I want to pull from from Sheet "22", matching text in a specific column ("Cherry-22"), and a row with multiple criteria ("Sacramento" and "Nancy". I also want to match the values in existing cells in my summary sheet, rather than typing values into a formula (i.e., D1 vs. "Cherry-22")

I tried to use an Index-Match to reference the column and row headers, but wasn't able to get a result at all. I tried adjusting, creating practice sheets, searching for examples, but I still have nothing.

Thanks!

ExcelPracticeMultipleCriteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Apple-22	Cherry-22	Peach-22	All-22	Apple-23	Cherry-23	Peach-23	All-23	Apple-24	Cherry-24	Peach-24	All-24
2	CA	Dan
3	CA	Nancy
4	CA	Joe
5	CA	Beth
6	CA	All
7	Sacramento	Dan
8	Sacramento	Nancy
9	Sacramento	Joe
10	Sacramento	Beth
11	Sacramento	All
12	San Francisco	Dan
13	San Francisco	Nancy
14	San Francisco	Joe
15	San Francisco	Beth
16	San Francisco	All
17	Los Angeles	Dan
18	Los Angeles	Nancy
19	Los Angeles	Joe
20	Los Angeles	Beth
21	Los Angeles	All
22	Bakersfield	Dan
23	Bakersfield	Nancy
24	Bakersfield	Joe
25	Bakersfield	Beth
26	Bakersfield	All
Summary

ExcelPracticeMultipleCriteria.xlsx
	A	B	C	D	E	F
1			Apple-22	Cherry-22	Peach-22	All-22
2	CA	Dan	14	12	13	39
3	CA	Nancy	14	9	17	40
4	CA	Joe	13	17	9	39
5	CA	Beth	23	12	8	43
6	CA	All	64	50	47	161
7
8	Sacramento	Dan	2	6	6	14
9	Sacramento	Nancy	7	5	6	18
10	Sacramento	Joe	5	3	3	11
11	Sacramento	Beth	6	5	4	15
12	Sacramento	All	20	19	19	58
13
14	San Francisco	Dan	5	1	3	9
15	San Francisco	Nancy	4	0	8	12
16	San Francisco	Joe	2	6	2	10
17	San Francisco	Beth	9	5	3	17
18	San Francisco	All	20	12	16	48
19
20	Bakersfield	Dan	7	5	4	16
21	Bakersfield	Nancy	3	4	3	10
22	Bakersfield	Joe	6	8	4	18
23	Bakersfield	Beth	8	2	1	11
24	Bakersfield	All	24	19	12	55
22

 

[ExceLoki]

i added some to the data table
---------------

Book1
	A	B	C	D	E	F
1			Apple-22	Cherry-22	Peach-22	All-22
2	CA	Dan	14	12	13	39
3	CA	Nancy	14	9	17	40
4	CA	Joe	13	17	9	39
5	CA	Beth	23	12	8	43
6	CA	All	64	50	47	161
7	Sacramento	Dan	2	6	6	14
8	Sacramento	Nancy	7	5	6	18
9	Sacramento	Joe	5	3	3	11
10	Sacramento	Beth	6	5	4	15
11	Sacramento	All	20	19	19	58
12	San Francisco	Dan	5	1	3	9
13	San Francisco	Nancy	4	0	8	12
14	San Francisco	Joe	2	6	2	10
15	San Francisco	Beth	9	5	3	17
16	San Francisco	All	20	12	16	48
17	Bakersfield	Dan	7	5	4	16
18	Bakersfield	Nancy	3	4	3	10
19	Bakersfield	Joe	6	8	4	18
20	Bakersfield	Beth	8	2	1	11
21	Bakersfield	All	24	19	12	55
22	Los Angeles	Dan	1	2	3	6
23	Los Angeles	Nancy	3	2	1	6
24	Los Angeles	Joe	4	5	6	15
25	Los Angeles	Beth	6	5	4	15
26	Los Angeles	All	14	14	14	42
Sheet2

----------------
results:
----------------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Apple-22	Cherry-22	Peach-22	All-22	Apple-23	Cherry-23	Peach-23	All-23	Apple-24	Cherry-24	Peach-24	All-24
2	CA	Dan	14	12	13	39
3	CA	Nancy	14	9	17	40
4	CA	Joe	13	17	9	39
5	CA	Beth	23	12	8	43
6	CA	All	64	50	47	161
7	Sacramento	Dan	2	6	6	14
8	Sacramento	Nancy	7	5	6	18
9	Sacramento	Joe	5	3	3	11
10	Sacramento	Beth	6	5	4	15
11	Sacramento	All	20	19	19	58
12	San Francisco	Dan	5	1	3	9
13	San Francisco	Nancy	4	0	8	12
14	San Francisco	Joe	2	6	2	10
15	San Francisco	Beth	9	5	3	17
16	San Francisco	All	20	12	16	48
17	Los Angeles	Dan	1	2	3	6
18	Los Angeles	Nancy	3	2	1	6
19	Los Angeles	Joe	4	5	6	15
20	Los Angeles	Beth	6	5	4	15
21	Los Angeles	All	14	14	14	42
22	Bakersfield	Dan	7	5	4	16
23	Bakersfield	Nancy	3	4	3	10
24	Bakersfield	Joe	6	8	4	18
25	Bakersfield	Beth	8	2	1	11
26	Bakersfield	All	24	19	12	55
Sheet1

Cell Formulas
Range	Formula
C2:N26	C2	=IFERROR(INDEX(Sheet2!$C$2:$F$26,MATCH($A2&$B2,Sheet2!$A$2:$A$26&Sheet2!$B$2:$B$26,0), MATCH(C$1,Sheet2!$C$1:$F$1,0)),"")

 

[roberk9]

i added some to the data table
---------------

Book1
	A	B	C	D	E	F
1			Apple-22	Cherry-22	Peach-22	All-22
2	CA	Dan	14	12	13	39
3	CA	Nancy	14	9	17	40
4	CA	Joe	13	17	9	39
5	CA	Beth	23	12	8	43
6	CA	All	64	50	47	161
7	Sacramento	Dan	2	6	6	14
8	Sacramento	Nancy	7	5	6	18
9	Sacramento	Joe	5	3	3	11
10	Sacramento	Beth	6	5	4	15
11	Sacramento	All	20	19	19	58
12	San Francisco	Dan	5	1	3	9
13	San Francisco	Nancy	4	0	8	12
14	San Francisco	Joe	2	6	2	10
15	San Francisco	Beth	9	5	3	17
16	San Francisco	All	20	12	16	48
17	Bakersfield	Dan	7	5	4	16
18	Bakersfield	Nancy	3	4	3	10
19	Bakersfield	Joe	6	8	4	18
20	Bakersfield	Beth	8	2	1	11
21	Bakersfield	All	24	19	12	55
22	Los Angeles	Dan	1	2	3	6
23	Los Angeles	Nancy	3	2	1	6
24	Los Angeles	Joe	4	5	6	15
25	Los Angeles	Beth	6	5	4	15
26	Los Angeles	All	14	14	14	42
Sheet2

----------------
results:
----------------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Apple-22	Cherry-22	Peach-22	All-22	Apple-23	Cherry-23	Peach-23	All-23	Apple-24	Cherry-24	Peach-24	All-24
2	CA	Dan	14	12	13	39
3	CA	Nancy	14	9	17	40
4	CA	Joe	13	17	9	39
5	CA	Beth	23	12	8	43
6	CA	All	64	50	47	161
7	Sacramento	Dan	2	6	6	14
8	Sacramento	Nancy	7	5	6	18
9	Sacramento	Joe	5	3	3	11
10	Sacramento	Beth	6	5	4	15
11	Sacramento	All	20	19	19	58
12	San Francisco	Dan	5	1	3	9
13	San Francisco	Nancy	4	0	8	12
14	San Francisco	Joe	2	6	2	10
15	San Francisco	Beth	9	5	3	17
16	San Francisco	All	20	12	16	48
17	Los Angeles	Dan	1	2	3	6
18	Los Angeles	Nancy	3	2	1	6
19	Los Angeles	Joe	4	5	6	15
20	Los Angeles	Beth	6	5	4	15
21	Los Angeles	All	14	14	14	42
22	Bakersfield	Dan	7	5	4	16
23	Bakersfield	Nancy	3	4	3	10
24	Bakersfield	Joe	6	8	4	18
25	Bakersfield	Beth	8	2	1	11
26	Bakersfield	All	24	19	12	55
Sheet1

Cell Formulas
Range	Formula
C2:N26	C2	=IFERROR(INDEX(Sheet2!$C$2:$F$26,MATCH($A2&$B2,Sheet2!$A$2:$A$26&Sheet2!$B$2:$B$26,0), MATCH(C$1,Sheet2!$C$1:$F$1,0)),"")

@ExceLoki
Yes, this worked! Thank you so much!

 

[ExceLoki]

glad to help