Formula will not read the output of another formula in a different cell

[Testsubject]

Hi, I am having difficulty getting a formula in B16 to read the output of another formula in A16.

A16) Has the current formula that outputs a value based on information in another cell (F3) in this case.............=IFERROR(VLOOKUP(F3,Q:R,2,FALSE),"#VALUE!").....The output value will always be 7710 or 3810.

B16) Has the current formula =_xlfn.IFS(A16="","#A15EMPTY#",A16="3810",_xlfn.CONCAT(_xlfn.IFS(LEN(F2)>=5,"#GL TOO LONG#",LEN(F2)=4,"000",LEN(F2)=3,"0000",LEN(F2)=2,"00000",LEN(F2)=1,"000000",LEN(F2)=0,"#EMPTY#"),F2),A16="7710",_xlfn.CONCAT(_xlfn.IFS(LEN(F2)>4,"#GL TOO LONG#",LEN(F2)=4,"",LEN(F2)=3,"",LEN(F2)=2,"0",LEN(F2)=1,"00",LEN(F2)="","#MISSING#GL#"),F2,"00"))

The challenge is that even though A16 has the output of 7710, B16 does not read this output and I want to avoid having to type 7710 or 3810 manually into A16 for B16 to read it.

 

[JamesCanale]

It looks like the formula was written in a later version of excel than you are running. All of the "_xlfn." business in the formula. If you are running 2016, "IFS" isn't there. Oddly, "CONCAT" aught to be, but it's also giving you the "_xlfn."

 

[Fluff]

"CONCAT" aught to be

Concat is only in xl 2019 & later.

 

[Testsubject]

My apologies, wrote it in office 365 and copied to xl 2016.

Should be without the "_xfln"

=IFS(A16="","#A15EMPTY#",A16="3810",CONCAT(IFS(LEN(F2)>=5,"#GL TOO LONG#",LEN(F2)=4,"000",LEN(F2)=3,"0000",LEN(F2)=2,"00000",LEN(F2)=1,"000000",LEN(F2)=0,"#EMPTY#"),F2),A16="7710",CONCAT(IFS(LEN(F2)>4,"#GL TOO LONG#",LEN(F2)=4,"",LEN(F2)=3,"",LEN(F2)=2,"0",LEN(F2)=1,"00",LEN(F2)="","#MISSING#GL#"),F2,"00"))