xlookup formula

[Assino960]

Hi,

i have two sheets and i try to search multiple criteria using xlookup formula, 

it works for the first cell but when i drag down to copy the formula it shows the #VALUE! error (column R)

[#2]

@Assino960 If your Excel version supports the new FILTER function, try this in R3:

=FILTER($F$1:$F$3641,($A$1:$A$3641=L3)*($D$1:$D$3641=$R$2))

and copy it down.

Replace commas by semi-colons in case your locale requires it. Attached your file with this formula in it, for reference.

[#3]

@Riny_van_Eekelen unfortunately my excel does not support the filter function.

[#4 · Accepted solution]

@Assino960 

That could be

=XLOOKUP(L3&"="&$R$2,$A$2:$A$3641&"="&$D$2:$D$3641,$F$2:$F$3641,"")

if you have XLOOKUP, otherwise INDEX/MATCH

=IFNA(INDEX($F$2:$F$3641,MATCH(L11&"="&$R$2,$A$2:$A$3641&"="&$D$2:$D$3641,0)),"")

Both are in the file.