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

Copper Contributor


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.


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

NameBirth YearGender Gender(Var1)Year(Var2)Name
John1987Male Male1982#VALUE!


4 Replies

@HaythamOlwan Try this:




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

It worked as well, thank you.


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.