Complex Left, Right, Mid Scenario

Attachment 461689

As you can see in the image above, I have a category column on one sheet. This column contains more than 100 distinct values. On another page I have the file name column with several thousand values. Essentially the category is contained within each file name and I want a column that contains the corresponding category that is found within the name. I tried a mid and find function. This would technically work except that there are 100 categories so you would need 100 nested statements which is neither feasible or possible with the 64 statement limit for excel. In addition, left and right functions would be difficult since the character count before and after the category in the name differs frequently. Is there any way to essentially the category within the cell? What would be the best way to go about solving this?

Try this...

Data Range

	A	B	C	D
1	Category	------	File Name	Category
2	Dog		Test_123_Dog_1234.txt	Dog
3	Cat		Test_125_Bird_1234.txt	Bird
4	Bird		Test_132_Bird_1234.txt	Bird
5			Test_125234532_Bird_123212234.txt	Bird
6			Test_121123425_Cat_1232222224.txt	Cat

This formula entered in D2 and copied down:

=IFERROR(LOOKUP(1000,SEARCH(A$2:A$4,C2),A$2:A$4),"")

Another way if the category is always between the 2nd and 3rd underscores...

=TRIM(MID(SUBSTITUTE(C2,"_",REPT(" ",100)),200,100))

Test_123_Dog_1234.txt

=IFERROR(LOOKUP(1000,SEARCH(A$2:A$4,C2),A$2:A$4),"")

If the search string is not found SEARCH returns the #VALUE! error...

SEARCH("Dog",C2) = 10
SEARCH("Cat",C2) = #VALUE!
SEARCH("Bird",C2) = #VALUE!

LOOKUP returns the value from A2:A4 that corresponds to the number value in the SEARCH array:

LOOKUP(1000,{10;#VALUE!;#VALUE!},A2:A4)

=A2

So...

=IFERROR(LOOKUP(1000,SEARCH(A$2:A$4,C2),A$2:A$4),"")

=Dog

You're welcome!

You're welcome!