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

 

[ecrodrig]

So for some reason, the formula isn't acting as it should. I created three helper columns instead and this is what I came up with:

Column 1 (A)
=XLOOKUP(A3,Sheet1!$A:$A,Sheet1!$C:$C, "Not Found")

Column 2 (B)
= XLOOKUP(A3,Sheet2!$A:$A,Sheet2!$E:$E, "Not Found")

Column 3 (C)
=XLOOKUP(A3,Sheet3!$A:$A,Sheet3!$C:$C,"Not Found")

Column 4 (D)
IF(AND(A3="Not Found", B3="Not Found", C3="Not Found"), "Not There", SUM(A3:C3))

This is working. I know it isn't the prettiest but it is providing the result I need for the time being. If anyone knows of a way to tie that above together then that would be awesome.

 

[6StringJazzer]

How can each XLOOKUP use A3 as the target value and also your formula in column D is looking for "Not Found" in A3?

You really need to copying these directly from Excel and pasting them into the post.

 

[ecrodrig]

How can each XLOOKUP use A3 as the target value and also your formula in column D is looking for "Not Found" in A3?

You really need to copying these directly from Excel and pasting them into the post.

This time I double-checked everything, my formulas are correct.

Let me explain:

This formula is going down a column. so basically A3 = Project 1 A4 = Project 2 and so on. With that said the first column is looking for Project 1 in Sheet 1 if it finds it then it is giving me the information in C3 (total price) on that same sheet (This sheet is T&Mcharges)

The next Column is checking for that same project (Project 1) In sheet 2 (OTC) if it finds it then it will give me the total price (E3) for that particular project.

The 3rd column is checking for that same project (Project 1) in sheet 3 (Service Charges) if it finds it then it will give me the total price (C3) for that particular project.

If Project 1 isn't found on any of those sheets then I will get Not Found.

On my total column I have IF(AND(A3="NF", B3="NF", C3="NF"), "Not Found", SUM(AA3:AC3))

So if all three columns have Not Found then I get Not Found if it finds the project then add all 3 columns and give me that #

Look at the picture attached. You can see what I mean. Hope this helps. Thanks.

 

[Alex Blakenburg]

How can each XLOOKUP use A3 as the target value and also your formula in column D is looking for "Not Found" in A3?

You really need to copying these directly from Excel and pasting them into the post.

This time I double-checked everything, my formulas are correct.

@ecrodrig - your picture was very helpful but perhaps not in the way you had intended.
It supports what Jeff is trying to say to you, that retyping formulas into a post seldom works out.

It is likely that some of the suggestions we made to you didn't work on your end for a similar reason.

We understand trying to work on 2 computers due to some work limitations can be tricky but perhaps see if you can email the document to yourself.

PS: Sum over a range is pretty forgiving. You don't need to use the IF statement. Just =SUM(AA3:AC3) will do it. It will treat the Text values as zero.
Hmm on 2nd thoughts that won't give you Not Found if all 3 are NF.

 

[ecrodrig]

I am trying to upload the mini sheet but I do not get that option. I have downloaded the add-in. I have a Mac so not sure if that is the problem.

 

[ecrodrig]

I am trying to upload the mini sheet but I do not get that option. I have downloaded the add-in. I have a Mac so not sure if that is the problem.

Sample_File_forecast (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	First Characters	New Business (RFS)	Jan	Feb	Mar	Apr	May	June	Jul	Aug	Sep	Oct	Nov	Dec	Jan_1	Feb_2	Mar_3	Apr_4	May_5	June_6	TOTALS	Is it found in AllPMData Tab	TCV	Type of billing	Left to bill	% Charged	% Left to Charged	Comments	T&M	OTC	New Service Charges	Total
2	Project 1	project 1 name	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 250.00	$ 60.00	$ 310.00	Found and the project is In Progress	$ 12222.42	T&M	$ 11,912.42	3%	97.46%		602.95	NF	NF	602.95
3	Project 2	project 2 name	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 100.00	$ 175.00	$ 275.00	Found and the project is In Progress	$ 1575.3	T&M	$ 1,300.30	17%	82.54%		363.5	1750	8500	10613.5
4	Project 3	project 3 name	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 75.00	$ 125.00	$ 200.00	Found and the project is Closed	$ 315	MRC	$ 115.00	63%	36.51%		1500	NF	1500	3000
5	Project 4	project 4 name	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 150.00	$ 125.00	$ 275.00	Found and the project is Closed	$ 2835.54	T&M	$ 2,560.54	10%	90.30%		2520	NF	NF	2520
6	Project 5	project 5 name	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 50.00	$ 50.00	Found and the project is Closed	$ 2835.54	T&M	$ 2,785.54	2%	98.24%		5500	NF	NF	5500
7	Project42	project42 name	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -		$ -	Found and the project is In Progress	$ 17173	OTC	$ 17,173.00	0%	100.00%		NF	NF	NF	Not Found
Billingfile

Cell Formulas
Range	Formula
T2:T7	T2	=_xlfn._xlws.FILTER('T&MMthlyFile'!$R$2:$R$12,('T&MMthlyFile'!$A$2:$A$12="Total")*('T&MMthlyFile'!$B$2:$B$12=$A2),"")
U2:U7	U2	=SUM(C2:T2)
V2:V7	V2	=LET(x, XLOOKUP(A2,AllData!F:F,AllData!A:A, ""),IF(x="", "Not found on AllPMData Tab","Found and the project is "&x&" "))
W2:W7	W2	=LET(x, XLOOKUP(A2,AllData!F:F,AllData!L:L,""),IF(x="", "Not found on AllData Tab"," $ "&x&" "))
X2:X7	X2	=LET(x, XLOOKUP(A2,AllData!F:F,AllData!K:K, ""),IF(x="", "Not found on AllPMData Tab"," "&x&" "))
Y2:Y7	Y2	=W2-U2
Z2:Z7	Z2	=100%-AA2
AA2:AA7	AA2	=Y2/W2
AC2	AC2	=XLOOKUP($A2,'T&M'!A:A,'T&M'!C:C,"NF")
AD2:AD7	AD2	=XLOOKUP($A2,OTC!$A:$A,OTC!$C:$C,"NF")
AE2:AE7	AE2	=XLOOKUP($A2,NewSevicecharges!A:A,NewSevicecharges!C:C,"NF")
AF2:AF7	AF2	=IF(AND(AC2="NF",AD2="NF",AE2="NF"),"Not Found", SUM(AC2:AE2))
AC3:AC7	AC3	=XLOOKUP(A3,'T&M'!A:A,'T&M'!C:C,"NF")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
AllData!_FilterDatabase	=AllData!$A$1:$Q$124	V2:V7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AA1:AA7	Other Type	Color scale		NO

 

[Alex Blakenburg]

Thanks. You seem to have a working version now. Did you want us to do something still ?
The filter command is showing in Red, do your computers have access to the Filter function ?

 

[ecrodrig]

Thanks. You seem to have a working version now. Did you want us to do something still ?
The filter command is showing in Red, do your computers have access to the Filter function ?

I just wanted to share the spreadsheet since I made such a mess of writing the formulas and sharing them here Appreciate your patience and willingness to help! As far as the filter not sure I downloaded excel on my mac and it seems that the Filter function isn't quite recognized so it added this part to the formula _xlfn._xlws. if I remove that from my formula in Mac the formula will not work.

Lastly, now that you know what I was trying to do, is there a way to merge the formulas from the helper columns (AC, AD, and AE) to get to my final total in AF?

If not it's OK, the helper columns are doing what they are meant to do help

 

[Alex Blakenburg]

It worries me a little that Filter isn't working properly for you. I would have thought Let would not be working either.
If Let does work then you could try this.

=LET(xTM,XLOOKUP(A2,'T&M'!$A:$A,'T&M'!$C:$C,"NF"),
        xOTC,XLOOKUP(A2,OTC!$A:$A,OTC!$C:$C,"NF"),
        xNewSvc,XLOOKUP(A2,NewSevicecharges!$A:$A,NewSevicecharges!$C:$C,"NF"),
        IF(AND(xTM="NF",xOTC="NF",xNewSvc="NF"),"Not Found",SUM(xTM,xOTC,xNewSvc)))

If not and you really want to do it in one step you can try the below:
but it is doing all the lookups twice which could make the spreadsheet really slow.

= IF(AND(XLOOKUP(A2,'T&M'!$A:$A,'T&M'!$C:$C,"NF") = "NF",
       XLOOKUP(A2,OTC!$A:$A,OTC!$C:$C,"NF") = "NF",
            XLOOKUP(A2,NewSevicecharges!$A:$A,NewSevicecharges!$C:$C,"NF") = "NF"),
    "Not Found",
     SUM(XLOOKUP(A2,'T&M'!$A:$A,'T&M'!$C:$C,0),
              XLOOKUP(A2,OTC!$A:$A,OTC!$C:$C,0),
              XLOOKUP(A2,NewSevicecharges!$A:$A,NewSevicecharges!$C:$C,0)))

Try to copy it into row 2 of your spreadsheet, typing it in is really a last resort even a single character out or place would break it.

 

[ecrodrig]

It worries me a little that Filter isn't working properly for you. I would have thought Let would not be working either.
If Let does work then you could try this.

=LET(xTM,XLOOKUP(A2,'T&M'!$A:$A,'T&M'!$C:$C,"NF"),
        xOTC,XLOOKUP(A2,OTC!$A:$A,OTC!$C:$C,"NF"),
        xNewSvc,XLOOKUP(A2,NewSevicecharges!$A:$A,NewSevicecharges!$C:$C,"NF"),
        IF(AND(xTM="NF",xOTC="NF",xNewSvc="NF"),"Not Found",SUM(xTM,xOTC,xNewSvc)))

If not and you really want to do it in one step you can try the below:
but it is doing all the lookups twice which could make the spreadsheet really slow.

= IF(AND(XLOOKUP(A2,'T&M'!$A:$A,'T&M'!$C:$C,"NF") = "NF",
       XLOOKUP(A2,OTC!$A:$A,OTC!$C:$C,"NF") = "NF",
            XLOOKUP(A2,NewSevicecharges!$A:$A,NewSevicecharges!$C:$C,"NF") = "NF"),
    "Not Found",
     SUM(XLOOKUP(A2,'T&M'!$A:$A,'T&M'!$C:$C,0),
              XLOOKUP(A2,OTC!$A:$A,OTC!$C:$C,0),
              XLOOKUP(A2,NewSevicecharges!$A:$A,NewSevicecharges!$C:$C,0)))

Try to copy it into row 2 of your spreadsheet, typing it in is really a last resort even a single character out or place would break it.

I copied both formulas to my spreadsheet on my windows machine. The first one is giving me a #VALUE! error, the second one works like a charm!