SOLVED

xlookup formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1803239%22%20slang%3D%22en-US%22%3Exlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1803239%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Ei%20have%20two%20sheets%20and%20i%20try%20to%20search%20multiple%20criteria%20using%20xlookup%20formula%2C%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20works%20for%20the%20first%20cell%20but%20when%20i%20drag%20down%20to%20copy%20the%20formula%20it%26nbsp%3B%3CSPAN%3Eshows%20the%20%23%3C%2FSPAN%3E%3CEM%3EVALUE%3C%2FEM%3E%3CSPAN%3E!%20error%20(column%20R)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1803239%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1803345%22%20slang%3D%22en-US%22%3ERe%3A%20xlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1803345%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F788581%22%20target%3D%22_blank%22%3E%40Assino960%3C%2FA%3E%26nbsp%3BIf%20your%20Excel%20version%20supports%20the%20new%20FILTER%20function%2C%20try%20this%20in%20R3%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(%24F%241%3A%24F%243641%2C(%24A%241%3A%24A%243641%3DL3)*(%24D%241%3A%24D%243641%3D%24R%242))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20copy%20it%20down.%3C%2FP%3E%3CP%3EReplace%20commas%20by%20semi-colons%20in%20case%20your%20locale%20requires%20it.%20Attached%20your%20file%20with%20this%20formula%20in%20it%2C%20for%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1803620%22%20slang%3D%22en-US%22%3ERe%3A%20xlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1803620%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bunfortunately%20my%20excel%20does%20not%20support%20the%20filter%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1804785%22%20slang%3D%22en-US%22%3ERe%3A%20xlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1804785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F788581%22%20target%3D%22_blank%22%3E%40Assino960%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(L3%26amp%3B%22%3D%22%26amp%3B%24R%242%2C%24A%242%3A%24A%243641%26amp%3B%22%3D%22%26amp%3B%24D%242%3A%24D%243641%2C%24F%242%3A%24F%243641%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20you%20have%20XLOOKUP%2C%20otherwise%20INDEX%2FMATCH%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(INDEX(%24F%242%3A%24F%243641%2CMATCH(L11%26amp%3B%22%3D%22%26amp%3B%24R%242%2C%24A%242%3A%24A%243641%26amp%3B%22%3D%22%26amp%3B%24D%242%3A%24D%243641%2C0))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EBoth%20are%20in%20the%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

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

 

Highlighted

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

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