Forum Discussion
XLOOK UP multiple criteria
Lycias The last optional argument is set to -1, which means that the search is done "last-to-first", so from the bottom up. Since your data is sorted in ascending year order, it works.
Lycias Since you intend to use XLOOKUP, you also have access to the FILTER function. Combine that one with XLOOKUP and achieve what you need. The attached file contains your data but transformed into structured tables. Didn't really see the need to concatenate the Subgroup and Area ID, so got rid of that column. In stead, I added a column for the first three characters of the Area ID so that the matching to the Codes in Sheet2 becomes easier. But it isn't necessary either as you see from the alternative solution in Sheet2. Also deleted the text based value column in Sheet1. You can achieve the same number formatting by applying a custom format.
Finally, I added some Data Validation based on a list of all existing (unique) age categories. That saves you from having to re-type them in the formulae, should you want to change the selection criteria.
Lycias The last optional argument is set to -1, which means that the search is done "last-to-first", so from the bottom up. Since your data is sorted in ascending year order, it works.
- Perfect, thank you so much!.