Forum Discussion
Solved
Excel look up values and select most recent year
Good day, I want a formula that look-up an indicator in could D, for a specific age group in column F for a specific country using the country ISO code in column H, then retrieves the most recent va...
See the attached version.
Thanks, Hans. Is it possible to have this as one formula that I can use in a column in another sheet to pull this value? I just want the data value in one column for all countries?
See the attached version.
- Hi Hans, I noticed that the formula is not giving me the data value for the most recent year. it seems like it is giving the data value for the first appearance of the Subgroup_val (column F). Is there any modification I can do so that the data value given is for the most recent year from column J?
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
- Thanks a lot, Hans. This works perfectly fine.