Please help with XLOOKUP error

[Colinahn]

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

 

Sheet 2

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?

[#2]

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