SOLVED

XLOOK UP multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2585206%22%20slang%3D%22en-US%22%3EXLOOK%20UP%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2585206%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EIn%20sheet%202%2C%20I%20want%20a%20formula%20in%20column%20B%20that%20picks%20for%20the%20latest%20year%20in%20column%20C%20of%20the%20Data%201%20sheet%2C%20a%20value%20in%20column%20B%20for%20a%20code%20in%20Column%20A%20of%20sheet%202%2C%20based%20on%20criteria%20in%20Data%201%20sheet%20Column%20A.%20The%20code%20in%20sheet%202%20is%20in%26nbsp%3B%20Column%20D%20Data%201%20sheet%20and%20has%20been%20concatenated%20with%20column%20G%20to%20get%20what%20you%20see%20in%20Column%20A.%20For%20example%2C%20if%20I%20want%20all%20values%20for%2025%2B%20for%20CHL%20for%20the%20latest%20year%20available%20in%20Colum%26nbsp%3B%20C%20of%20the%20Data%201%20sheet.%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2585206%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2585643%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOK%20UP%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2585643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749945%22%20target%3D%22_blank%22%3E%40Lycias%3C%2FA%3E%26nbsp%3BSince%20you%20intend%20to%20use%20XLOOKUP%2C%20you%20also%20have%20access%20to%20the%20FILTER%20function.%20Combine%20that%20one%20with%20XLOOKUP%20and%20achieve%20what%20you%20need.%20The%20attached%20file%20contains%20your%20data%20but%20transformed%20into%20structured%20tables.%20Didn't%20really%20see%20the%20need%20to%20concatenate%20the%20Subgroup%20and%20Area%20ID%2C%20so%20got%20rid%20of%20that%20column.%20In%20stead%2C%20I%20added%20a%20column%20for%20the%20first%20three%20characters%20of%20the%20Area%20ID%20so%20that%20the%20matching%20to%20the%20Codes%20in%20Sheet2%20becomes%20easier.%20But%20it%20isn't%20necessary%20either%20as%20you%20see%20from%20the%20alternative%20solution%20in%20Sheet2.%20Also%20deleted%20the%20text%20based%20value%20column%20in%20Sheet1.%20You%20can%20achieve%20the%20same%20number%20formatting%20by%20applying%20a%20custom%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%2C%20I%20added%20some%20Data%20Validation%20based%20on%20a%20list%20of%20all%20existing%20(unique)%20age%20categories.%20That%20saves%20you%20from%20having%20to%20re-type%20them%20in%20the%20formulae%2C%20should%20you%20want%20to%20change%20the%20selection%20criteria.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2585709%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOK%20UP%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2585709%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20Thank%20you%20so%20much%2C%20a%20lifesaver.%20I%20see%20that%20the%20formula%20is%20picking%20the%20latest%20value%20(Time%2C%20in%20column%20A%20of%20sheet%201)%20for%20each%20country%20code.%20Which%20part%20of%20this%20formula%20is%20achieving%20this%3F%20I%20can%20follow%20the%20rest%20of%20the%20solution%20except%20this%20part.%20Thank%20you.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

In sheet 2, I want a formula in column B that picks for the latest year in column C of the Data 1 sheet, a value in column B for a code in Column A of sheet 2, based on criteria in Data 1 sheet Column A. The code in sheet 2 is in  Column D Data 1 sheet and has been concatenated with column G to get what you see in Column A. For example, if I want all values for 25+ for CHL for the latest year available in Colum  C of the Data 1 sheet.

Thank you!

4 Replies

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

 

@Riny_van_Eekelen Thank you so much, a lifesaver. I see that the formula is picking the latest value (Time, in column A of sheet 1) for each country code. Which part of this formula is achieving this? I can follow the rest of the solution except this part. Thank you.
best response confirmed by Lycias (Occasional Contributor)
Solution

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