Excel look up values and select most recent year

[Lycias]

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

[#2]

@Lycias 

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

See the attached version.

[#3]

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?

[#4 · Accepted solution]

@Lycias 

See the attached version.

[#5]

Thanks a lot, Hans. This works perfectly fine.

[#6]

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?

[#7]

@Lycias 

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:

[#8]

@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

[#9]

@Lycias 

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

[#10]

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

[#11]

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

[#12]

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

[#13]

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.

[#14]

@Lycias 

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