Forum Discussion

Judgin's avatar
Judgin
Copper Contributor
Dec 07, 2022

Help me complete the formula

Hello, The formula works almost correctly but the issue with the last row. There is the code 190#93#91#25#12#17 and I need to do that if the last number ( in this example 17) missing in the colu...
Formulas and Functions
office 365
HansVogelaar's avatar
HansVogelaar
MVP
Dec 07, 2022

Judgin 

If you'll only need the penultimate number, you can use

 

=LET(cat,TEXTSPLIT(B2,"#"),XLOOKUP(--INDEX(cat,COUNTA(cat)),$E$2:$E$5,$F$2:$F$5,XLOOKUP(--INDEX(cat,COUNTA(cat)-1),$E$2:$E$5,$F$2:$F$5)))

 

If you might need to search further back, it would be better to use LAMBDA.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 08, 2022

    HansVogelaar 

    I went through your formula to see what it was doing

    = LET(
    last2,  TAKE(TEXTSPLIT(target, "#"), ,-2),
    final,  --INDEX(last2, 2),
    penult, --INDEX(last2, 1),
    XLOOKUP(final, lookupArr, returnArr, 
      XLOOKUP(penult, lookupArr, returnArr)
    )
  )

    About the only difference is that I homed in on TAKE rather than COUNTA to pick up the final values.  I liked the use of the second XLOOKUP.  It is easy to overlook the fact that 'if not found' will take a formula and not just text.

  • Judgin's avatar
    Judgin
    Copper Contributor
    Dec 08, 2022
    Thank you very much! It works for me.

Resources