Yesterday I was so fortunate to receive help on a question (How to work with IF/IFS/ISBLANK when columns contains zero length strings), which yielded the following answers:
=BYROW(CA6:CC13; LAMBDA(a; INDEX(a; XMATCH(TRUE; ISNUMBER(1/a); 0; -1))))
=BYROW(CA6:CC13; LAMBDA(a; XLOOKUP(TRUE; ISNUMBER(1/a); a; ; 0; -1)))
=IF(1-ISERR(1/CC6:CC13); CC6:CC13; IF(1-ISERR(1/CB6:CB13); CB6:CB13; CA6:CA13))
All of the functions work perfectly for the assigment. However, I now need help modifing these(or just one of them), so the function will return not only numbers but also text.
For the purpose of quality check of the workbook, I am not allowed to use VBA.
This is the results the function in column CD should look like:
The function provided by Mayukh Bhattacharya almost work. But this function only returns text from the last column (CA).
I have tried to rewrite the function to the following:
=IF(ISNUMBER(1/BK6:BK12), BK6:BK12, IF(ISNUMBER(1/BJ6:BJ12), BJ6:BJ12, BI6:BI12))
Also tried this:
=IF(OR(ISNUMBER(1/CC6:CC13), ISTEXT(CC6:CC13)), IFERROR(CC6:CC13, ""), IF(OR(ISNUMBER(1/CB6:CB13), ISTEXT(CB6:CB13)), IFERROR(CB6:CB13, ""), CA6:CA13))
>Solution :
This works for me:
=LET(_Fx, LAMBDA(α, (1-ISERR(1/α))+(ISTEXT(α))),
IF(_Fx(CC6:CC13),CC6:CC13,IF(_Fx(CB6:CB13),CB6:CB13,IF(_Fx(CA6:CA13),CA6:CA13))))
Updated Versions of the above:
=LET(
_Fx, LAMBDA(α, (1-ISERR(1/α))+(ISTEXT(α)*(α<>""))),
IFS(_Fx(CC6#), CC6#, _Fx(CB6#), CB6#, _Fx(CA6#), CA6#))