Index/Match based on header names rather than columns

[jreda]

Hi all

Assuming therre is a simple solution, have been looking around and yet to find an answer which is probably a reflection on my keyword search...

The below is a quick example I've pulled together. I'm using index/match to calculate the total # of fruit in france on Jul23.

I'm wanting to update my formula to lookup based on the headings rather than being tied to a column, e.g. updating my formula to lookup vegtables rather than needing to edit the formula to pull results from column D based on the condtions (month and country).

Hope that makes sense and apologies if the solution is super obvious.

 

[kevin9999]

If you mean add a third condition to get the total, how about this:

Book1
	A	B	C	D	E	F	G	H
1	Month	Country	Fruit	Vegetables	Snacks		Month	Country
2	Jul-23	UK	10	1	9		Jul-23	UK
3	Jul-23	US	30	2	10		Aug-23	US
4	Jul-23	Italy	45	3	11			Italy
5	Jul-23	France	50	4	12			France
6	Aug-23	UK	34	5	13
7	Aug-23	US	65	6	14
8	Aug-23	Italy	68	7	15
9	Aug-23	France	90	8	16
10
11	Conditions
12	Month	Jul-23
13	Country	France
14	Type'	Vegetables
15
16	Answer:	4
17
Sheet1

Cell Formulas
Range	Formula
G2:G3,H2:H5	G2	=UNIQUE(A2:A9)
E2:E9	E2	=D2+8
B16	B16	=SUMPRODUCT((A2:A9=B12)*(B2:B9=B13)*(C1:E1=B14),C2:E9)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B12	List	=$G$2#
B13	List	=$H$2#
B14	List	=$C$1:$E$1

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Is this what you are after?
If it is possible that more than one row meets the "Conditions" then wrap the given formula in a SUM function like this

=SUM(FILTER(FILTER(C2:E9,C1:E1=B14),(A2:A9=B12)*(B2:B9=B13),0))

23 11 16.xlsm
	A	B	C	D	E
1	Month	Country	Fruit	Vegetables	Snacks
2	Jul-23	UK	10	1	9
3	Jul-23	US	30	2	10
4	Jul-23	Italy	45	3	11
5	Jul-23	France	50	4	12
6	Aug-23	UK	34	5	13
7	Aug-23	US	65	6	14
8	Aug-23	Italy	68	7	15
9	Aug-23	France	90	8	16
10
11	Conditions:
12	Month	Jul-23
13	Country	France
14	Type	Fruit
15
16	Answer	50
Lookup 2

Cell Formulas
Range	Formula
B16	B16	=FILTER(FILTER(C2:E9,C1:E1=B14),(A2:A9=B12)*(B2:B9=B13),0)

 

[jreda]

Welcome to the MrExcel board!

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Is this what you are after?
If it is possible that more than one row meets the "Conditions" then wrap the given formula in a SUM function like this

=SUM(FILTER(FILTER(C2:E9,C1:E1=B14),(A2:A9=B12)*(B2:B9=B13),0))

23 11 16.xlsm
	A	B	C	D	E
1	Month	Country	Fruit	Vegetables	Snacks
2	Jul-23	UK	10	1	9
3	Jul-23	US	30	2	10
4	Jul-23	Italy	45	3	11
5	Jul-23	France	50	4	12
6	Aug-23	UK	34	5	13
7	Aug-23	US	65	6	14
8	Aug-23	Italy	68	7	15
9	Aug-23	France	90	8	16
10
11	Conditions:
12	Month	Jul-23
13	Country	France
14	Type	Fruit
15
16	Answer	50
Lookup 2

Cell Formulas
Range	Formula
B16	B16	=FILTER(FILTER(C2:E9,C1:E1=B14),(A2:A9=B12)*(B2:B9=B13),0)

Excellent, thanks so much Peter_SSs, this filter function is exactly what I'm needing!

Noted on the XL2BB comment and thanks, will make use of this in the future.

I know this is an excel forum, but any chance you know how this formula would translate to google sheets? I'm working in both platforms....

 

[Peter_SSs]

Excellent, thanks so much Peter_SSs, this filter function is exactly what I'm needing!

You're welcome. Thanks for the follow-up.

any chance you know how this formula would translate to google sheets?

I rarely use Google Sheets so I generally don't answer questions about it here.
Did you try @kevin9999's suggestion in either platform?

If that doesn't do what you want I suggest that you start a new thread in the General Discussion & Other Applications forum as that is the place for Google Sheets questions. In that thread you could put a link to this one so any helpers are aware of what has already happened here.

 

[jreda]

You're welcome. Thanks for the follow-up.


I rarely use Google Sheets so I generally don't answer questions about it here.
Did you try @kevin9999's suggestion in either platform?

If that doesn't do what you want I suggest that you start a new thread in the General Discussion & Other Applications forum as that is the place for Google Sheets questions. In that thread you could put a link to this one so any helpers are aware of what has already happened here.

Thanks Peter_SSs I've just tried @kevin9999's solution and it worked.

Thank you both for the quick helpful repsonses! Saved me a bunch of time, I should have come here earlier....

 

[Peter_SSs]

Glad we could help.