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.
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.
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")
You can send me a private message (click on my username)
- 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.
- that other formula I was just trying out something. I forgot to delete it before attaching it. let me try again this and see.