Xlookup returning multiple not adjacent columns

[INN]

Hi
I am trying to make xlookup returning mutiple values. So I enter name and want xlookup to return phone number and salary. But it is not working. Is there anyway to retun multiple non adjacent values?
Thank you very much.

distinct-pivottable.xlsx
	A	B	C	D	E	F	G
1	Name	phone	Address	Salary
2	Chanelle	1	1 main street	10		Noah	220
3	Noah	2	2 main street	20
4	Sienna	3	3 main street	30
5	Goldie	4	4 main street	40
6	Logan	5	5 main street	50
7	Elisabeth	6	6 main street	60
8	Thea	7	7 main street	70
9	Gil	8	8 main street	80
10	Ron	9	9 main street	90
11	Alan	10	10 main street	100
Sheet4

Cell Formulas
Range	Formula
G2	G2	=XLOOKUP(F2,A2:A11,B2:B11&D2:D11)

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=$A$2:$A$30

 

[psgoldberg]

With your table named tbl: =XLOOKUP(F2,tbl[Name],CHOOSE({1,2,3},tbl[Name],tbl[Phone],tbl[Salary])) or just use RC references if tables are not your thing.

 

[INN]

Thanks for your reply. Can it be done without using other fucntion like Choose(). I thought it can be done using Xlookup alone. Thank you.

 

[AhoyNC]

You're formula works for me. Looks like it worked in your example. What issue are you seeing?

 

[psgoldberg]

No. As good as XLOOKUP is, returning non-adjacent columns without other contortions is not possible as best I know.

 

[AhoyNC]

If you want them in separate cells then you could use either VLOOKUP or FILTER.

Book1
	A	B	C	D	E	F	G	H
1	Name	phone	Address	Salary			Phone	Salary
2	Chanelle	1	1 main street	10		Goldie	4	40
3	Noah	2	2 main street	20			4	40
4	Sienna	3	3 main street	30
5	Goldie	4	4 main street	40
6	Logan	5	5 main street	50
7	Elisabeth	6	6 main street	60
8	Thea	7	7 main street	70
9	Gil	8	8 main street	80
10	Ron	9	9 main street	90
11	Alan	10	10 main street	100
Sheet5

Cell Formulas
Range	Formula
G2:H2	G2	=VLOOKUP(F2,A2:D11,{2,4},0)
G3:H3	G3	=FILTER(FILTER(B2:D11,A2:A11=F2),{1,0,1})
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=$A$2:$A$11

 

[AhoyNC]

Another option would be XLOOKUP with FILTER:

=XLOOKUP(F2,A2:A11,FILTER(B2:D11,{1,0,1}))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Name	phone	Address	Salary
2	Chanelle	1	1 main street	10		Noah	2	20
3	Noah	2	2 main street	20
4	Sienna	3	3 main street	30
5	Goldie	4	4 main street	40
6	Logan	5	5 main street	50
7	Elisabeth	6	6 main street	60
8	Thea	7	7 main street	70
9	Gil	8	8 main street	80
10	Ron	9	9 main street	90
11	Alan	10	10 main street	100
Main

Cell Formulas
Range	Formula
G2:H2	G2	=CHOOSECOLS(XLOOKUP(F2,A2:A11,B2:D11),1,3)
Dynamic array formulas.

 

[RoryA]

Or yet another:

=XLOOKUP(F2,A2:A11,HSTACK(B2:B11,D2:D11))