SOLVED

xlookup formula

Occasional Contributor

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)

3 Replies

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

 

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

best response confirmed by Assino960 (Occasional Contributor)
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.