Forum Discussion

Eqa33's avatar
Eqa33
Copper Contributor
Dec 03, 2022
Solved

How to match and assign a word to a letter in a cell

Hi There, I have a column of product numbers, within each number is one letter that represents the type of product it is. How do I marry  that letter up to a description from another list so that I ...
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Dec 04, 2022

    Eqa33 You missed the most important part and that is that you need to extract the one Letter from the code first. You are now trying to merge the full product code in the Sales table with the category code in the Category table.  Obviously, that's not going to return any match.

    I've added the step that extracts the code and changed the merge query. It works now.

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 03, 2022

Hi Eqa33 

 

Assuming you run Excel 2021 or 365 a formula based alternative:

 

 

in C5:

=LET(
  LetterPos, SUM(IFERROR(SEARCH(CHAR(SEQUENCE(26,,97)),B5),0)),
  Letter,    MID(B5, LetterPos, 1),
  Category,  XLOOKUP(Letter, TBL_MAPPING[Letter], TBL_MAPPING[Category]),
  IF(LetterPos, Category, "No letter found")
)

Resources