IFS with Xlookup not working as it should

[ecrodrig]

So I have the following formula:

By themselves, they work as they should:

=XLOOKUP(A3,('Sheet1'!$A$A,'Sheet1'!$C$C,"Not Found")
=XLOOKUP(A3,('Sheet2'!$A$A,'Sheet2'!$E$E,"Not Found")
=XLOOKUP(A3,('Sheet3'!$A$A,'Sheet3'!$C$C,"Not Found")


Once I add them to an IFS statement I get #N/A Obviously I am missing something in my formula.

=IFS(XLOOKUP(A3,('Sheet1'!$A$A,'Sheet1'!$C$C), XLOOKUP(A3,('Shee2'!$A$A,'Sheet2'!$E$E), XLOOKUP(A3,('Sheet3'!$A$A,'Sheet3'!$C$C), "Not Found")


Any help would be appreciated. Thanks

 

[6StringJazzer]

The syntax for your first three formulas is wrong so I don't know how they can possibly work. You have unbalanced parentheses. I think they would work if you omit the second left parenthesis from each one.

The syntax for IFS is:

=IFS(expression1, result if expression1 is True, expression2, result if expression2 is True, expression3, result if expression3 is True....)

Looking at your formula, I can't match it up with the required syntax. And as above, you have unbalanced parentheses.

What do you want this formula to do?

 

[ecrodrig]

this is what the formula should do:

If you find A3 in Sheet 1 then give me the information on C3, If this isn't true move on to looking for A3 in sheet 2 and if found give me the information in E3, if this isn't true then try to find A3 in sheet 3 and if found then give me the information in C3 if none of the three arguments are true then tell me Not Found

I tested them individually and they all work individually

Argument one:
=XLOOKUP(A3,('Sheet1'!$A$A,'Sheet1'!$C$C,"Not Found")

Argument two:
=XLOOKUP(A3,('Sheet2'!$A$A,'Sheet2'!$E$E,"Not Found")

Argument three:
=XLOOKUP(A3,('Sheet3'!$A$A,'Sheet3'!$C$C,"Not Found")

 

[ecrodrig]

Ha! resolved my own problem. No IFS needed I could just add all the Xlookup arguments together like this:

=XLOOKUP(A3,('Sheet1'!$A$A,'Sheet1'!$C$C,XLOOKUP(A3,('Sheet2'!$A$A,'Sheet2'!$E$E,XLOOKUP(A3,('Sheet3'!$A$A,'Sheet3'!$C$C,"Not Found")))

THANKS!!

 

[Alex Blakenburg]

You have to be transcribing your formulas because you are posting formulas that don't work.
As Jeff said you have 2 left brackets but only 1 right closing bracket.
Also your ranges are missing the ":".

In your final solution formula you are still missing the ":" And have the extra "("
Should be:

=XLOOKUP(A3,Sheet1!$A:$A,Sheet1!$C:$C,
       XLOOKUP(A3,Sheet2!$A:$A,Sheet2!$E:$E,
            XLOOKUP(A3,Sheet3!$A:$A,Sheet3!$C:$C,"Not Found")))

 

[ecrodrig]

You have to be transcribing your formulas because you are posting formulas that don't work.
As Jeff said you have 2 left brackets but only 1 right closing bracket.
Also your ranges are missing the ":".

In your final solution formula you are still missing the ":" And have the extra "("
Should be:

=XLOOKUP(A3,Sheet1!$A:$A,Sheet1!$C:$C,
       XLOOKUP(A3,Sheet2!$A:$A,Sheet2!$E:$E,
            XLOOKUP(A3,Sheet3!$A:$A,Sheet3!$C:$C,"Not Found")))

Sorry, You are correct I am using 2 different computers and instead of copying and pasting, I have to write the formulas out. In my excel workbook, I had it correct so the formula works. Thanks for correcting it here. I did come up with one more interesting situation.

What if I have a scenario where A3 is found on 2 sheets or on all 3 sheets? In that case, I need the totals added together. So something like this:

If A3 is found in Sheet 1 and Sheet 2 then add C3 and E3 and give me that output, if A3 is found on all 3 sheets then add C3+E3+C3 and give me that output

I need to add those conditions to my current code :

=XLOOKUP(A3,Sheet1!$A:$A,Sheet1!$C:$C,
       XLOOKUP(A3,Sheet2!$A:$A,Sheet2!$E:$E,
            XLOOKUP(A3,Sheet3!$A:$A,Sheet3!$C:$C,"Not Found")))

 

[Alex Blakenburg]

I was initially going to say use SumIfs but across sheets you would still need to add the 3 formulas.
Go back to your original formulas and replace the "Not Found" with 0 and add them up.
If you still want Not Found as an option the formula will get more complex.

=XLOOKUP(A3,Sheet1!$A:$A,Sheet1!$C:$C,0) +
       XLOOKUP(A3,Sheet2!$A:$A,Sheet2!$E:$E,0) +
            XLOOKUP(A3,Sheet3!$A:$A,Sheet3!$C:$C,0)

 

[ecrodrig]

I was initially going to say use SumIfs but across sheets you would still need to add the 3 formulas.
Go back to your original formulas and replace the "Not Found" with 0 and add them up.
If you still want Not Found as an option the formula will get more complex.

=XLOOKUP(A3,Sheet1!$A:$A,Sheet1!$C:$C,0) +
       XLOOKUP(A3,Sheet2!$A:$A,Sheet2!$E:$E,0) +
            XLOOKUP(A3,Sheet3!$A:$A,Sheet3!$C:$C,0)

I will test this and let you know. Thanks Alex!

 

[ecrodrig]

Hi, your solution worked, as it is doing what it should. I will now search on how I can covert the 0 into not found. Thanks again!

 

[Alex Blakenburg]

Unfortunately the only way I know to do that is to do the lookup twice. Someone else might have a more efficient solution.
This might get really slow especially since you are using "whole column" referencing. ie A:A. It would better if you set a range of rows even if the upper limit is quite generous say 100k rows. That is still better than whole colums which is 1M rows.

=IF(XLOOKUP(A3,Sheet1!$A:$A,Sheet1!$C:$C,
         XLOOKUP(A3,Sheet2!$A:$A,Sheet2!$E:$E,
            XLOOKUP(A3,Sheet3!$A:$A,Sheet3!$C:$C,"Not Found")))
                 ="Not Found",
                 "Not Found",
                 XLOOKUP(A3,Sheet1!$A:$A,Sheet1!$C:$C,0) +
                     XLOOKUP(A3,Sheet2!$A:$A,Sheet2!$E:$E,0) +
                     XLOOKUP(A3,Sheet3!$A:$A,Sheet3!$C:$C,0))