Please help with XLOOKUP error

%3CLINGO-SUB%20id%3D%22lingo-sub-2006253%22%20slang%3D%22en-US%22%3EPlease%20help%20with%20XLOOKUP%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2006253%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20find%20the%20PO%20value%20in%20sheet%201%20that%20meets%20multiple%20criteria%20using%20sheet%202.%3C%2FP%3E%3CP%3E(Sorry%2C%20I%20am%20not%20able%20to%20upload%20the%20sample%20file%20due%20to%20the%20policy.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-12-21%20at%2021.33.06.png%22%20style%3D%22width%3A%20589px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242070iDA33C9650FCF02B4%2Fimage-dimensions%2F589x165%3Fv%3D1.0%22%20width%3D%22589%22%20height%3D%22165%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-12-21%20at%2021.33.06.png%22%20alt%3D%22Screen%20Shot%202020-12-21%20at%2021.33.06.png%22%20%2F%3E%3C%2FSPAN%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%202%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-12-21%20at%2021.33.18.png%22%20style%3D%22width%3A%20390px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242071i777BB50B1710A086%2Fimage-dimensions%2F390x173%3Fv%3D1.0%22%20width%3D%22390%22%20height%3D%22173%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-12-21%20at%2021.33.18.png%22%20alt%3D%22Screen%20Shot%202020-12-21%20at%2021.33.18.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20the%20function%20I%20used%20is%26nbsp%3B%3DXLOOKUP(1%2C%20(A2%3D'Sheet%202'!C2%3AC6)*(C2%3D'Sheet%202'!D2%3AD6)*(C1%3D'Sheet%202'!B2%3AB6)%2C%20'Sheet%202'!A2%3AA6)%2C%20but%20it%20showed%20N%2FA%20error.%3C%2FP%3E%3CP%3EIt%20works%20if%20I%20remove%26nbsp%3B(C1%3D'Sheet%202'!B2%3AB6)%2C%20but%20I%20need%20this%20to%20look%20up%20and%20return%20the%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20should%20I%20modify%20to%20fix%20this%20error%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2006253%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2006404%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%20with%20XLOOKUP%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2006404%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F871690%22%20target%3D%22_blank%22%3E%40Colinahn%3C%2FA%3E%26nbsp%3BTry%20this%20one%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(XLOOKUP(%24A2%26amp%3BC%241%2C'Sheet%202'!%24C%242%3A%24C%246%26amp%3B'Sheet%202'!%24B%242%3A%24B%246%2C'Sheet%202'!%24A%242%3A%24A%246)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BReplicated%20your%20file%20to%20demonstrate.%20See%20attached.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

I'm trying to find the PO value in sheet 1 that meets multiple criteria using sheet 2.

(Sorry, I am not able to upload the sample file due to the policy.)

 

Sheet 1

Screen Shot 2020-12-21 at 21.33.06.png

 

Sheet 2

Screen Shot 2020-12-21 at 21.33.18.png

 

So, the function I used is =XLOOKUP(1, (A2='Sheet 2'!C2:C6)*(C2='Sheet 2'!D2:D6)*(C1='Sheet 2'!B2:B6), 'Sheet 2'!A2:A6), but it showed N/A error.

It works if I remove (C1='Sheet 2'!B2:B6), but I need this to look up and return the value.

 

What should I modify to fix this error?

 

1 Reply

@Colinahn Try this one;

=IFERROR(XLOOKUP($A2&C$1,'Sheet 2'!$C$2:$C$6&'Sheet 2'!$B$2:$B$6,'Sheet 2'!$A$2:$A$6),"")

 Replicated your file to demonstrate. See attached.