XLOOKUP return multiple column and ignore blanks

[Garmerr]

Hello Community,
I am trying to use the Xlookup function to return values that match my look up value. The values in the lookup table are spread across multiple columns and not all columns have values. I would like to return the lookup values and to exclude any blank values.

Currently, Xlookup brings back all of the values which includes the blanks and returns as 0. Would like to ignore the blanks and just move to the next column with a value in it.

Any ideas on the formula? I am running 365.

Lookup Value

Dog

Cat

Fish

Bird

Dog

Bird

Lookup Table
Name	Value 1	Value 2	Value 3	Value 4
Dog	Furry		Big
Cat	Furry	Small		Brown
Fish				Yellow
Bird		Small		Red

Ideal Result
Name	Result 1	Result 2	Result 3	Result X
Dog	Furry	Big
Cat	Furry	Small	Brown
Fish	Yellow
Bird	Small	Red
Dog	Furry	Big
Bird	Small	Red

 

[dreid1011]

Perhaps this:

Book1 1-4-2024.xlsx
	A	B	C	D	E
1	Name	Result 1	Result 2	Result 3	Result X
2	Dog	Furry	Big
3	Cat	Furry	Small	Brown
4	Fish	Yellow
5	Bird	Small	Red
6	Dog	Furry	Big
7	Bird	Small	Red
8
9	Name	Value 1	Value 2	Value 3	Value 4
10	Dog	Furry		Big
11	Cat	Furry	Small		Brown
12	Fish				Yellow
13	Bird		Small		Red
Sheet7

Cell Formulas
Range	Formula
B2:C2,B5:C7,B4,B3:D3	B2	=LET(x,XLOOKUP(A2,$A$10:$A$13,$B$10:$E$13,,0),FILTER(x,x<>0))
Dynamic array formulas.

 

[Peter_SSs]

I am running 365.

Please add that to your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

Another possible option

24 01 06.xlsm
	A	B	C	D	E
1	Name	Result 1	Result 2	Result 3	Result X
2	Dog	Furry	Big
3	Cat	Furry	Small	Brown
4	Fish	Yellow
5	Bird	Small	Red
6	Dog	Furry	Big
7	Bird	Small	Red
8
9	Name	Value 1	Value 2	Value 3	Value 4
10	Dog	Furry		Big
11	Cat	Furry	Small		Brown
12	Fish				Yellow
13	Bird		Small		Red
Remove blanks

Cell Formulas
Range	Formula
B2:C2,B5:C7,B4,B3:D3	B2	=TOROW(FILTER(B$10:E$13,A$10:A$13=A2),1)
Dynamic array formulas.

 

[Garmerr]

Perhaps this:

Book1 1-4-2024.xlsx
	A	B	C	D	E
1	Name	Result 1	Result 2	Result 3	Result X
2	Dog	Furry	Big
3	Cat	Furry	Small	Brown
4	Fish	Yellow
5	Bird	Small	Red
6	Dog	Furry	Big
7	Bird	Small	Red
8
9	Name	Value 1	Value 2	Value 3	Value 4
10	Dog	Furry		Big
11	Cat	Furry	Small		Brown
12	Fish				Yellow
13	Bird		Small		Red
Sheet7

Cell Formulas
Range	Formula
B2:C2,B5:C7,B4,B3:D3	B2	=LET(x,XLOOKUP(A2,$A$10:$A$13,$B$10:$E$13,,0),FILTER(x,x<>0))
Dynamic array formulas.

This method works. Thanks for reply.

 

[Garmerr]

Please add that to your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

Another possible option

24 01 06.xlsm
	A	B	C	D	E
1	Name	Result 1	Result 2	Result 3	Result X
2	Dog	Furry	Big
3	Cat	Furry	Small	Brown
4	Fish	Yellow
5	Bird	Small	Red
6	Dog	Furry	Big
7	Bird	Small	Red
8
9	Name	Value 1	Value 2	Value 3	Value 4
10	Dog	Furry		Big
11	Cat	Furry	Small		Brown
12	Fish				Yellow
13	Bird		Small		Red
Remove blanks

Cell Formulas
Range	Formula
B2:C2,B5:C7,B4,B3:D3	B2	=TOROW(FILTER(B$10:E$13,A$10:A$13=A2),1)
Dynamic array formulas.

Awesome. I found this solution to be a bit more efficient. Either of these will work.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.