Matching a value if a code in a table is within another cell

[MrBKirk]

I need a formula that in column F will search and match a table where Column I's value is within Column A and make F egual to the equivalent value in J.
I've tried search, match and Vlookup to no luck.
Eg,

• I2 is within A2 so B2 should equal to J2
• I3 is within A5 so B5 should equal to J3

 

[awoohaw]

AC (cell I2) is in the first three rows. Why is A2 the choice?

 

[MrBKirk]

AC (cell I2) is in the first three rows. Why is A2 the choice?

Sorry, I was just providing 2 examples, apologies that this was unclear.

Any cells in column A containing the code from column I should make the equivalent B equal to the matching J.
So F3 and F4 should also become J2.

 

[Asbestos_Jen]

Are the target letters always found in the same position in your course codes? In the example, the two letters you are looking for are all at characters 3 and 4.

 

[MrBKirk]

Are the target letters always found in the same position in your course codes? In the example, the two letters you are looking for are all at characters 3 and 4.

Characters 3 and 4 are always the start of the course, but unfortunately, 5th characters are also sometimes required. For example, FIC = Food With Imagination and FIT = Personal Fitness. But other times the 5th character no longer relates to the course, for example, 7AHUA7A and 7BHUB7A both should be equal to Humanities and the A & B are not relevant to the course.

Not sure why the person who set it up this way did not make the course codes consistent in format...

 

[Asbestos_Jen]

Characters 3 and 4 are always the start of the course, but unfortunately, 5th characters are also sometimes required. For example, FIC = Food With Imagination and FIT = Personal Fitness. But other times the 5th character no longer relates to the course, for example, 7AHUA7A and 7BHUB7A both should be equal to Humanities and the A & B are not relevant to the course.

Not sure why the person who set it up this way did not make the course codes consistent in format...

How long is the list of categories? It would be an ungainly but doable "if" if there's not too many more, but otherwise, I see needing some helper columns to extract the relevant bits of the course numbers.
Do the examples I made up for FIT and FIC meet normal courses, or are there extra/missing characters?

CourseCodes.xlsx
	A					F	G	H	I	J
1	Subject					Name
2	1AAC1								AC	Accounting
3	1BAC1								AR	Art
4	3AAC1								BI	Biology
5	0AART2								BM	Business Management
6	7AHUA7A								FIC	Food with Imagination
7	7BHUB7A								FIT	Personal Fitness
8	4AFIC2A								HU	Humanities
9	4AFIT1A
10
11
Sheet2

 

[Asbestos_Jen]

This is what I came up with. Note: I made up a code for physics to extend the list a little, and a math course number with no math in the list of codes just to see what would happen if the target was not in the list.
Credit for the sumproduct extraction in column L to Excel Search a Cell for a List of Words

CourseCodes.xlsx
	A					F	G	H	I	J	K	L
1	Subject					Name						extracted letters
2	1AAC1					Accounting			AC	Accounting		AC
3	1BAC1					Accounting			AR	Art		AC
4	3AAC1					Accounting			BI	Biology		AC
5	0AART2					Art			BM	Business Management		AR
6	7AHUA7A					Humanities			FIC	Food with Imagination		HU
7	7BHUB7A					Humanities			FIT	Personal Fitness		HU
8	4AFIC2A					Food with Imagination			HU	Humanities		FIC
9	4AFIT1A					Personal Fitness			PH	Physics		FIT
10	2APH4B					Physics						PH
11	2AMA4B					#VALUE!						#VALUE!
Sheet2

Cell Formulas
Range	Formula
F2:F11	F2	=INDEX($I$2:$J$9,MATCH(L2,$I$2:$I$9,0),2)
L2:L11	L2	=INDEX($I$2:$I$9,SUMPRODUCT(ISNUMBER(SEARCH($I$2:$I$9,A2))*(ROW($I$2:$I$9)-1)))

 

[Alex Blakenburg]

I am only on a phone at the moment so can't give you the exact formula but based on your response to @Asbestos_Jen in post 5, how about a vlookup on mid(a2,3, 3), wrap it in iferror with the error condition being a vlookup on mid(a2 3,2).
ie if lookup on 3 characters fails try 2.

 

[Peter_SSs]

As per Alex's suggestion, you could try this.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 11 03.xlsm
	A					F	G	H	I	J
1	Subject					Name
2	1AAC1					Accounting			AC	Accounting
3	1BAC1					Accounting			AR	Art
4	3AAC1					Accounting			BI	Biology
5	0AART2					Art			BM	Business Management
6	7AHUA7A					Humanities			FIC	Food with Imagination
7	7BHUB7A					Humanities			FIT	Personal Fitness
8	4AFIC2A					Food with Imagination			HU	Humanities
9	4AFIT1A					Personal Fitness			PH	Physics
10	2APH4B					Physics
11	2AMA4B					#N/A
Lookup Code

Cell Formulas
Range	Formula
F2:F11	F2	=IFNA(VLOOKUP(MID(A2,3,3),I$2:J$9,2,0),VLOOKUP(MID(A2,3,2),I$2:J$9,2,0))

 

[MrBKirk]

This is what I came up with. Note: I made up a code for physics to extend the list a little, and a math course number with no math in the list of codes just to see what would happen if the target was not in the list.
Credit for the sumproduct extraction in column L to Excel Search a Cell for a List of Words

Thanks Asbestos_Jen, those formulas are fantastic, I will certainly keep them in mind for future reference. Unfortunately, there are almost 400 lines of unique subjects and over 50 in the reference table. 70 rows returned errors, more due to idiosyncrasies with the formatting of the subject codes than your formula though.

I am only on a phone at the moment so can't give you the exact formula but based on your response to @Asbestos_Jen in post 5, how about a vlookup on mid(a2,3, 3), wrap it in iferror with the error condition being a vlookup on mid(a2 3,2).
ie if lookup on 3 characters fails try 2.

Thanks so much Alex! worked almost perfectly, with only one issue with 0PHO = Photography, 0PHY = Physics and 3PH = Physics resulting in all three becoming physics, but I'm just going to have the original creator use 3PHY in the future to rectify this problem.

As per Alex's suggestion, you could try this.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

I'll certainly look into XL2BB and give it a try in the future, wasn't aware of it as I just created the account for this problem. Thanks for the suggestion and apologies for the inconvenience everyone!

Thank you all again for your input and support! This will save us a lot of manual processing!