SOLVED

# Excel look up values and select most recent year

Brass Contributor

# 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 value using year in column J, and the indicator value is in column L. Please see attached dummy data.

Thanks,

Lycias

13 Replies

# Re: Excel look up values and select most recent year

The formula is a bit more complicated since all values in your sample worksheet are text.

See the attached version.

# Re: Excel look up values and select most recent year

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?
best response confirmed by Lycias (Brass Contributor)
Solution

# Re: Excel look up values and select most recent year

See the attached version.

# Re: Excel look up values and select most recent year

Thanks a lot, Hans. This works perfectly fine.

# Re: Excel look up values and select most recent year

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?

# Re: Excel look up values and select most recent year

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:

# Re: Excel look up values and select most recent year

@Hans Vogelaar 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

# Re: Excel look up values and select most recent year

Please attach a sample workbook. Pasting sample data into a reply doesn't work the same way.

# Re: Excel look up values and select most recent year

@Hans Vogelaar it is picking the 2014 values for the 15-19 and 15-49 age groups and not the latest year which is 2018 values.

# Re: Excel look up values and select most recent year

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")

# Re: Excel look up values and select most recent year

that other formula I was just trying out something. I forgot to delete it before attaching it. let me try again this and see.

# Re: Excel look up values and select most recent year

Revisiting this thread...Is it possible I to email you directly? I am nos sure why I still do not get the latest value of the indicator based on the most recent year.

# Re: Excel look up values and select most recent year

You can send me a private message (click on my username)