Forum Discussion

RobG92's avatar
RobG92
Copper Contributor
Oct 22, 2020

Writing a formula to return a specific value from a table.

Hello community,

I'm having trouble making a formula, maybe I'm just over thinking it so I'm here to ask for help!

I have a excel document with two sheet tabs: See attached for example.

  • Program Table
  • Category Table

What I need

I need a formula that will look through column B, which contains categories that show up at different times, on the left. 

Find the category in each cell and return the corresponding ID found in the "Category Table" tab.

 

The trick here is that some of the cells will contain specific text such as "News" or "Toon" as well as extra text. So, I'm really hoping for a formula that will find that similar text in a cell, and provide the ID for the program.
For example. If it says "Toon: revenge of the villain" I want the same ID for "Toon:" to be applied.

 

Thank you,

One of my attempts is attached. 

 

excel
Formulas and Functions

3 Replies

  • Bennadeau's avatar
    Bennadeau
    Iron Contributor
    Oct 22, 2020

    Hi RobG92,

    This will work is you always have a ":" after the first word in a situation where you have more then 1 word in your Category.

    =IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1=1,VLOOKUP(B2,Category,2,0),VLOOKUP(IF(ISERR(FIND(":",B2)),"",LEFT(B2,FIND(":",B2)-1)),Category,2,0))

    See attached file.

    • RobG92's avatar
      RobG92
      Copper Contributor
      Oct 22, 2020
      This seems to do the trick, but in my example, it unfortunately doesn't cover the full breadth of variations in categories.

      The colon might appear twice in some category titles.

      Like "All Stars: Monster Edition: The Shark's revenge" for example. Your formula doesn't seem to work for this condition. But if I change the reference to accommodate, it works out.
      What would I need to do to make this work for the above example?
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 22, 2020

        RobG92 

        If I understood correctly something like this shall be returned

        Formula could be

        =IF(
        SUMPRODUCT(
          ISNUMBER(SEARCH('Category Table'!$A$2:$A$4,B2))*
          (ROW('Category Table'!$A$2:$A$4)-ROW('Category Table'!$A$1))
        ),
        INDEX('Category Table'!$B$2:$B$4,
             SUMPRODUCT(
                ISNUMBER(SEARCH('Category Table'!$A$2:$A$4,B2))*
                (ROW('Category Table'!$A$2:$A$4)-ROW('Category Table'!$A$1))
            )
        ),
"---")

Resources