Search code examples
excelif-statementtextexcel-formula

How to return text and not just numbers with arrays in Excel

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:
enter image description here

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:

    enter image description here

    =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:

    enter image description here

    =LET(
     _Fx, LAMBDA(α, (1-ISERR(1/α))+(ISTEXT(α)*(α<>""))),
     IFS(_Fx(CC6#), CC6#, _Fx(CB6#), CB6#, _Fx(CA6#), CA6#))

    And this one may be shorter I think:

    enter image description here

    =BYROW(CA6:CC21,LAMBDA(α,LOOKUP(2,1/((α<>"")*(α<>0)),α)))