May 11 2022 01:31 AM
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 |
May 11 2022 01:41 AM
@HaythamOlwan Try this:
=FILTER(A2:A5,(B2:B5=F2)*(C2:C5=E2))
May 11 2022 01:41 AM
=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.
May 11 2022 01:47 AM
Dec 02 2022 11:51 AM
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)