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

New Contributor

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.

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

 

3 Replies

@HaythamOlwan Try this:

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

@HaythamOlwan 

=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.

It worked as well, thank you.