Averaging Data with Multiple Lookups

[FuriousD]

I have 2 workbooks - one with raw data and one where I want to average the data based on multiple rows of source data.

An example of the data is below.

Data Workbook Name: CA Data Export
Results Workbook Name: FF Data

A B C D E F G

Job Code	Job Title	Geography	Industry	Size	Base 25th	Base 50th
SC16000631	Diesel Mechanic Supervisor	Chicago, IL	Consumer Goods	$1B - $3B Revenues ($ USD)	63.8​	70.8​
SC16000631	Diesel Mechanic Supervisor	Chicago, IL	Consumer Goods	7,500 - 15,000 FTEs	62.9​	69.8​
SC16000631	Diesel Mechanic Supervisor	Chicago, IL	Food & Beverage	$1B - $3B Revenues ($ USD)	62.2​	69​
SC16000631	Diesel Mechanic Supervisor	Chicago, IL	Food & Beverage	7,500 - 15,000 FTEs	61.3​	68​

I then need to pull the average of the Base 25th and Base 50th given the Job Code and Geography. I'm using an Index & Match formula to pull the data, but it's only returning the first result, not an average of the 4 lines of data.

My Index/Match Formula (below) returns the following results in Cell D2/E2:

=INDEX('CA Data Export'!$F$1:$F$1977,MATCH('FF Data'!A2&'FF Data'!B2,'CA Data Export'!$A$1:$A$1977&'CA Data Export'!$C$1:$C$1977,0))


A B C D E

Job Code	JobTitle	CA Scope	Base 25th	Base 50th
SC16000389	Diesel Mechanic Supervisor	Chicago, IL	63.8​	70.8​	​

Any thoughts on how I would get it to return the average of the 4 lines?

TIA

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also do you mean two workbooks, or two worksheets? The formula looks as though it's looking at two sheets in the same workbook.

 

[FuriousD]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also do you mean two workbooks, or two worksheets? The formula looks as though it's looking at two sheets in the same workbook.

Sorry -

Correct - 2 worksheets in the same Workbook. And I'm on Excel 365 Excel for Mac v16.52

 

[Fluff]

In that case you can use averageifs like

=AVERAGEIFS('CA Data Export'!$F$1:$F$1977,'CA Data Export'!$A$1:$A$1977,A2,'CA Data Export'!$C$1:$C$1977,B2)

although I think the B2 on the end might need to be C2.

Also you should never use the name of the sheet the formula is on, as it can cause problems.

 

[FuriousD]

Awesome - and so simple. I was way over complicating it.

Thanks

 

[Fluff]

Glad to help & thanks for the feedback.