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 column E, then it should take the penultimated number (in this example 12). And the correct value in the cell G11 should be "Parent category 1".

https://docs.google.com/spreadsheets/d/1_c-pBYr5TRlipKdhAzkMaUXCSq5H0MVA/edit?usp=share_link&ouid=107091203855789194942&rtpof=true&sd=true 



Formulas and Functions
office 365

3 Replies

  • 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