INDEX and MATCH formula

[greevek]

It's been some time since I have needed to write more complex formulas and I am struggling, can anyone help with the below:

I am trying to put a formula together that looks up and compares data in two columns and returns the wording from one if it matches/partially matches the wording in the other column.
So far it is kind of working but it's not returning all of the data when I know it should be returning a match, I think I need to include EXACT somewhere in my formula so that it looks at case sensitive matches but I cannot figure out what to do.

Any help, guidance or alternative options will be greatly received.

Book.xlsx
	B			E					J
1	advert_identifier			companyname					Column3
2	RSM BEARES			RSM BEARES					RSM BEARES
3	INTERIORS			LIFE HOUSE ACCOUNT					NEW HOMES MAG
4	USED LISTING			Brandon Car Centre -MG					#N/A
5	FUW INSURANCE			FUW Insurance Services					FUW INSURANCE
6	BULLARDS			Bullards Spirits Ltd					BULLARDS
7	NNGM NFK			North Norfolk Garden Machinery					#N/A
8	BURE VALLEY RAILWAY			Bure Valley Railway (1991) Ltd					BURE VALLEY RAILWAY
9	ABBEYGATE LIGHTING			Rush Lighting Ltd					#N/A
10	NORFOLK GARDEN MAC			North Norfolk Garden Machinery					NNGM NFK
11	NORFOLK GARDEN MAC			North Norfolk Garden Machinery					NNGM NFK
12	DISS CARS			Diss Car Centre					#N/A
13	NORFOLK GARDEN MAC			North Norfolk Garden Machinery					NNGM NFK
14	DREAM DOORS			DREAM DOORS HUNTINGDON					DREAM DOORS
15	NORFOLK GARDEN MAC			North Norfolk Garden Machinery					NNGM NFK
16	A W MYHILLS			A W Myhill & Son					#N/A
17	HARTINGTON CHEESE			Hartington Cheese Shop					HARTINGTON CHEESE
18	LACY SCOTT & KNIGHT			Lacy Scott & Knight ( Fine Art A/C )					LACY SCOTT & KNIGHT
19	WINCER K - HAD HALF			Wincer Kievenaar LLP					#N/A
20	QC61135			Suffolk County Council					#N/A
21	ROLEX / WHITTLES			Whittles Jewellers					#N/A
22	QC61134			Suffolk County Council					#N/A
23	GIANT BOOTSALE FRIDA			tony sheen					#N/A
24	WINCER K - HAD FP			Wincer Kievenaar LLP					#N/A
25	GTI ROOFING - GOR			GTI Roofing					#N/A
26	HEALTH & MOBILITY			health & mobility					HEALTH & MOBILITY
27	NEW HOMES MAG			Nordikka Interiors Ltd					#N/A
28	CARS WANTED			Ipswich Road Service Station					#N/A
29	KJ CUSTOM KITCHENS			KJ Custom Outdoor Kitchens Ltd					#N/A
30	ROARR! - NSTA			Roarr! Dinosaur Adventure					#N/A
All Data

Cell Formulas
Range	Formula
J2:J30	J2	=INDEX($B$2:$E$4001,MATCH("*"&B2&"*",$E$2:$E$4001,0),1)

 

[steve the fish]

Im confused. In this example you could just do =E2 and drag it down.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[greevek]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you, just done that

 

[greevek]

Im confused. In this example you could just do =E2 and drag it down.

I don't want to know exactly what is in column E I want to return the words that are shown in column B that match the words in Column E. My formula is doing that but it doesn't seem to be doing it for all data even though I can see it should be.

 

[steve the fish]

Unfortunately i can see it shouldnt be. For example A W MYHILLS isnt found in column E hence the error. Same with all the other errors.

 

[greevek]

I don't want to know exactly what is in column E I want to return the words that are shown in column B that match the words in Column E. My formula is doing that but it doesn't seem to be doing it for all data even though I can see it should be.

I don't understand, for example, the word Wincer is mentioned in row 19 and 24 but it isn't pulling that data back, the same with Roarr! on 30, I would expect that to return it. Can you explain why this wouldn't return a result, please?

 

[steve the fish]

You arent asking it to match 'Wincer' you are asking it to match 'Wincer K - Had Half'

 

[greevek]

You arent asking it to match 'Wincer' you are asking it to match 'Wincer K - Had Half'

How do I get it to match Wincer, I thought adding the wild card in that would do that but I clearly got that wrong? Can you offer any help as to how I can achieve what I have asked?

 

[steve the fish]

What the wildcard does is essentially say does the cell CONTAIN the string rather than EQUAL the string. I dont think your data is suited to really helping. For 'wincer' we could extract the 'wincer' part then search for that. However that isnt necessarily appropriate for your entire data and could lead to errors. Think unfortunately you need to be more bulletproof with your naming and/ or data collection. Maybe create a lookup table to convert the column B names to useful names. Hard for me to tell as dont know where this data all comes from.