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
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
Any thoughts on how I would get it to return the average of the 4 lines?
TIA
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