# Is there a way to use FILTER function within XLOOKUP function?

Copper Contributor

# Is there a way to use FILTER function within XLOOKUP function?

Hi,

I am trying to retrieve data from a table based on two variable, so I thought of using FILTER function to limit the options within the Xlookup array to the needed values based on the the second variable, like below.

=XLOOKUP(F2,B2:B5,FILTER(A2:A5,C2:C5=E2),"")

It gives me #VALUE Error like in the below sample.

 Name Birth Year Gender Gender(Var1) Year(Var2) Name John 1987 Male Male 1982 #VALUE! George 1982 Male Sarah 1985 Female Mila 1998 Female

4 Replies

# Re: Is there a way to use FILTER function within XLOOKUP function?

@HaythamOlwan Try this:

=FILTER(A2:A5,(B2:B5=F2)*(C2:C5=E2))

# Re: Is there a way to use FILTER function within XLOOKUP function?

``=FILTER(\$A\$2:\$A\$5,(\$C\$2:\$C\$5=E2)*(\$B\$2:\$B\$5=F2))``

You can try to include both criteria in the FILTER function.

# Re: Is there a way to use FILTER function within XLOOKUP function?

It worked as well, thank you.

# Re: Is there a way to use FILTER function within XLOOKUP function?

I know it's been awhile and this has already been solved but I came across this and wanted to offer another solution. You can use multiple variables in an xlookup by using an "&". Just make sure your lookup values are in the same order as you lookup array. With the below formula, you get the name George for a male born in 1982.

=Xlookup(E2&F2,C2:C5&B2:B5,A2:A5)