Forum Discussion
Solved
xlookup formula
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)
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.
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.
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.
