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.