MicrosoftI am not aware of a setting that will allow XLOOKUP to return multiple results from non-adjacent columns. There are however a number of ways of combining XLOOKUP with other array functions to get the result you hope for.
1) You could build an array of output columns using CHOOSE
= XLOOKUP( name, Data[Name], CHOOSE( {1,2}, Data[ID], Data[Value] ) )
2) You could achieve the same starting point by filtering down to pre-select the output columns
= XLOOKUP( name, Data[Name], FILTER( Data, {1,0,0,0,5} ) )
3) You could output an entire record or part thereof and only then filter it
= FILTER( XLOOKUP( name, Data[Name], Data ), {1,0,0,0,5} )
4) Even INDEX could be used to pick out the results you specify
= INDEX( XLOOKUP( name, Data[Name], Data ), {1,5} )
My preference would be 1) because I prefer to reference data by name than by index unless it is the position that is important and not the content. Maybe others have better solutions but this will do me for starters 🙂
Edit: 5) A nested XLOOKUP would also work
= XLOOKUP( {"ID","Value"}, Data[#Headers], XLOOKUP( name, Data[Name], Data ) )
