SOLVED

Excel look up values and select most recent year

Brass Contributor

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

@Lycias 

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

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

@Lycias 

See the attached version.

Thanks a lot, Hans. This works perfectly fine.
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?

@Lycias 

The formula should do what you want. Take this example from the Dummydata sheet:

HansVogelaar_0-1689244542874.png

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_1-1689244693990.png

@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_deleteData_NIdsectorIndicator_NameUnit_NameSubgroup_ValArea_NameArea_IDArea_LevelTimePeriodSourceData_ValueTextual_Data_Value
 257114. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales 15-19CameroonCMR22014UNAIDS_GAM_36.1  
 257434. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales 20-24CameroonCMR22014UNAIDS_GAM_40.3  
 257584. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales Adults (15-49)CameroonCMR22014UNAIDS_GAM_32.7  
 1237484. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales 15-19CameroonCMR220182018 DHS20.0  
 1237494. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales 20-24CameroonCMR220182018 DHS22.6  
 1237504. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales 25-49CameroonCMR220182018 DHS21.4  
 1237514. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales Adults (15-49)CameroonCMR220182018 DHS21.5  
 1334594. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales Adults (15-49)CameroonCMR220182018 DHS21.5  
 1334604. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales 15-19CameroonCMR220182018 DHS20.0  
 1334614. Gender and discriminationPrevalence of recent intimate partner violencePercentFemales 20-24CameroonCMR220182018 DHS22.6  

 

@Lycias 

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

@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.

@Lycias 

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

that other formula I was just trying out something. I forgot to delete it before attaching it. let me try again this and see.
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.

@Lycias 

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

1 best response

Accepted Solutions
best response confirmed by Lycias (Brass Contributor)
Solution

@Lycias 

See the attached version.

View solution in original post