xlookup formula

Occasional Contributor


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:


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)


That could be


if you have XLOOKUP, otherwise INDEX/MATCH


Both are in the file.