Forum Discussion
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. Progr...
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.
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?
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?
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)) ) ), "---")
