Formula Help - XLookup + average

geraldvassallo 

If you want to return 0 from the average of the three XLOOKUPs in the case where the email address is not found on one sheet, then you can use this (KPI 3 as example):

=MIN(100%,IFERROR(AVERAGE(Target!$C$3/XLOOKUP(A4,'Data 3'!K:K,'Data 3'!C:C),Target!$C$3/XLOOKUP(A4,'Data 2'!K:K,'Data 2'!C:C),Target!$C$3/XLOOKUP(A4,'Data 1'!K:K,'Data 1'!C:C)),0))

Put simply, wrap the AVERAGE function in IFERROR and pass a zero as the second parameter to IFERROR.

I removed a lot of the parentheses as they were confusing me and they weren't strictly necessary.

You can just as easily wrap the MIN with IFERROR in the same way and it will be the same result.

I did notice that you seem to be using $C$3 as the numerator in each of the score calculations - I wonder if you meant this to be different for each XLOOKUP? i.e. for XLOOKUP using A:A, use Target!$C$3. For B:B, use Target!$C$4, and so on.

Additionally, I think you meant to use A:A for the return array from the data sheets for KPI 1 on the second user. When I opened your file, it was showing B:B, but the row above showed A:A.

Let me know if it was your intention to return a zero in this way. Note that since the mean KPI score is zero, the component of the efficiency expression that references that zero will also be zero, and so will be ignored as you need.