Index/Match based on header names rather than columns

jreda

New Member
Joined
Nov 16, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

1700111029623.png
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
If you mean add a third condition to get the total, how about this:
Book1
ABCDEFGH
1MonthCountryFruitVegetablesSnacksMonthCountry
2Jul-23UK1019Jul-23UK
3Jul-23US30210Aug-23US
4Jul-23Italy45311Italy
5Jul-23France50412France
6Aug-23UK34513
7Aug-23US65614
8Aug-23Italy68715
9Aug-23France90816
10
11Conditions
12MonthJul-23
13CountryFrance
14Type'Vegetables
15
16Answer:4
17
Sheet1
Cell Formulas
RangeFormula
G2:G3,H2:H5G2=UNIQUE(A2:A9)
E2:E9E2=D2+8
B16B16=SUMPRODUCT((A2:A9=B12)*(B2:B9=B13)*(C1:E1=B14),C2:E9)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B12List=$G$2#
B13List=$H$2#
B14List=$C$1:$E$1
 
Upvote 0
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
Excel Formula:
=SUM(FILTER(FILTER(C2:E9,C1:E1=B14),(A2:A9=B12)*(B2:B9=B13),0))


23 11 16.xlsm
ABCDE
1MonthCountryFruitVegetablesSnacks
2Jul-23UK1019
3Jul-23US30210
4Jul-23Italy45311
5Jul-23France50412
6Aug-23UK34513
7Aug-23US65614
8Aug-23Italy68715
9Aug-23France90816
10
11Conditions:
12MonthJul-23
13CountryFrance
14TypeFruit
15
16Answer50
Lookup 2
Cell Formulas
RangeFormula
B16B16=FILTER(FILTER(C2:E9,C1:E1=B14),(A2:A9=B12)*(B2:B9=B13),0)
 
Upvote 1
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
Excel Formula:
=SUM(FILTER(FILTER(C2:E9,C1:E1=B14),(A2:A9=B12)*(B2:B9=B13),0))


23 11 16.xlsm
ABCDE
1MonthCountryFruitVegetablesSnacks
2Jul-23UK1019
3Jul-23US30210
4Jul-23Italy45311
5Jul-23France50412
6Aug-23UK34513
7Aug-23US65614
8Aug-23Italy68715
9Aug-23France90816
10
11Conditions:
12MonthJul-23
13CountryFrance
14TypeFruit
15
16Answer50
Lookup 2
Cell Formulas
RangeFormula
B16B16=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....
 
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top