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

%3CLINGO-SUB%20id%3D%22lingo-sub-3356001%22%20slang%3D%22en-US%22%3EIs%20there%20a%20way%20to%20use%20FILTER%20function%20within%20XLOOKUP%20function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3356001%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20retrieve%20data%20from%20a%20table%20based%20on%20two%20variable%2C%20so%20I%20thought%20of%20using%20FILTER%20function%20to%20limit%20the%20options%20within%20the%20Xlookup%20array%20to%20the%20needed%20values%20based%20on%20the%20the%20second%20variable%2C%20like%20below.%3C%2FP%3E%3CP%3E%3DXLOOKUP(F2%2CB2%3AB5%2CFILTER(A2%3AA5%2CC2%3AC5%3DE2)%2C%22%22)%3C%2FP%3E%3CP%3EIt%20gives%20me%20%23VALUE%20Error%20like%20in%20the%20below%20sample.%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100.2754820936639%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2214.325068870523417%25%22%20height%3D%2221%22%3EName%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EBirth%20Year%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EGender%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EGender(Var1)%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EYear(Var2)%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EName%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2214.325068870523417%25%22%20height%3D%2221%22%3EJohn%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E1987%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EMale%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EMale%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E1982%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%23VALUE!%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2214.325068870523417%25%22%20height%3D%2221%22%3EGeorge%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E1982%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EMale%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2214.325068870523417%25%22%20height%3D%2221%22%3ESarah%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E1985%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EFemale%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2214.325068870523417%25%22%20height%3D%2221%22%3EMila%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E1998%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3EFemale%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2214.325068870523417%25%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3356001%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3356057%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20way%20to%20use%20FILTER%20function%20within%20XLOOKUP%20function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3356057%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1386802%22%20target%3D%22_blank%22%3E%40HaythamOlwan%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CP%3E%3DFILTER(A2%3AA5%2C(B2%3AB5%3DF2)*(C2%3AC5%3DE2))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3356058%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20way%20to%20use%20FILTER%20function%20within%20XLOOKUP%20function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3356058%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1386802%22%20target%3D%22_blank%22%3E%40HaythamOlwan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DFILTER(%24A%242%3A%24A%245%2C(%24C%242%3A%24C%245%3DE2)*(%24B%242%3A%24B%245%3DF2))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EYou%20can%20try%20to%20include%20both%20criteria%20in%20the%20FILTER%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3356089%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20there%20a%20way%20to%20use%20FILTER%20function%20within%20XLOOKUP%20function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3356089%22%20slang%3D%22en-US%22%3EIt%20worked%20as%20well%2C%20thank%20you.%3C%2FLINGO-BODY%3E
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.