Forum Discussion
Excel look up values and select most recent year
See the attached version.
The formula should do what you want. Take this example from the Dummydata sheet:
The most recent year for Antiretroviral therapy, Total, GTM is 2019, and the corresponding data value is 18.4
Here is the result of the formula:
HansVogelaar for some reason when I use same formular it doesnt seem to pick the most recent (year) data value. I used the following =IFERROR(--INDEX(GAM_historical_data_2023!$L$2:$L$11400,MATCH(1,(GAM_historical_data_2023!$D$2:$D$11400="Prevalence of recent intimate partner violence")*(GAM_historical_data_2023!$F$2:$F$11400="Females Adults (15-49)")*(GAM_historical_data_2023!$H$2:$H$11400=$A34)*(GAM_historical_data_2023!$J$2:$J$11400=""&MAX(IF((GAM_historical_data_2023!$D$2:$D$11400="Prevalence of recent intimate partner violence")*(GAM_historical_data_2023!$F$2:$F$11400="Females Adults (15-49)")*(GAM_historical_data_2023!$H$2:$H$11400=$A34),--GAM_historical_data_2023!$J$2:$J$11400))),0)),"id")
for this dummay data:
| update_delete | Data_NId | sector | Indicator_Name | Unit_Name | Subgroup_Val | Area_Name | Area_ID | Area_Level | TimePeriod | Source | Data_Value | Textual_Data_Value | |
| 25711 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females 15-19 | Cameroon | CMR | 2 | 2014 | UNAIDS_GAM_ | 36.1 | |||
| 25743 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females 20-24 | Cameroon | CMR | 2 | 2014 | UNAIDS_GAM_ | 40.3 | |||
| 25758 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females Adults (15-49) | Cameroon | CMR | 2 | 2014 | UNAIDS_GAM_ | 32.7 | |||
| 123748 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females 15-19 | Cameroon | CMR | 2 | 2018 | 2018 DHS | 20.0 | |||
| 123749 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females 20-24 | Cameroon | CMR | 2 | 2018 | 2018 DHS | 22.6 | |||
| 123750 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females 25-49 | Cameroon | CMR | 2 | 2018 | 2018 DHS | 21.4 | |||
| 123751 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females Adults (15-49) | Cameroon | CMR | 2 | 2018 | 2018 DHS | 21.5 | |||
| 133459 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females Adults (15-49) | Cameroon | CMR | 2 | 2018 | 2018 DHS | 21.5 | |||
| 133460 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females 15-19 | Cameroon | CMR | 2 | 2018 | 2018 DHS | 20.0 | |||
| 133461 | 4. Gender and discrimination | Prevalence of recent intimate partner violence | Percent | Females 20-24 | Cameroon | CMR | 2 | 2018 | 2018 DHS | 22.6 |
Please attach a sample workbook. Pasting sample data into a reply doesn't work the same way.
HansVogelaar it is picking the 2014 values for the 15-19 and 15-49 age groups and not the latest year which is 2018 values.
I am confused now. The formula in this sample workbook returns "id" indicating that there is no match.
This is because
- This is not the formula that I proposed.
- Unlike the formula that I proposed, it does not take into account that column J contains text values, not numbers.
Why did you decide to use a different formula?
By the way, the correct version of the formula that you used would be
=IFERROR(--INDEX($L$2:$L$100, MATCH(MAX(IF($D$2:$D$100="Prevalence of recent intimate partner violence", IF($F$2:$F$100="Females 15-19", IF($G$2:$G$100=Q2, --$J$2:$J$100)))), --$J$2:$J$100, 0)), "id")