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)

 

[Peter_SSs]

Thanks for updating your profile.

Can you offer any help as to how I can achieve what I have asked?

What you have asked is not clear.

Adding to what steve the fish has already said ..
Your current formula, using "*"&B2&"*", is checking for the whole of each column B value within column E values.
For example, "BULLARDS" (cell B6) is found within "Bullards Spirits Ltd" (E6) and hence "BULLARDS" is returned as a result.

"NORFOLK GARDEN MAC" (B10) is first found in "North Norfolk Garden Machinery" (E8) and so "NNGM NFK" (B8) is returned, being the corresponding value to that first match.
Is that what you wanted for "NORFOLK GARDEN MAC" or did you want "NORFOLK GARDEN MAC" returned?

If you want to search for every individual word in column B (eg "Wincer") then I think you will have further problems as Excel will see words as those groups of characters separated by spaces. That would make, for example, both "A" and "W" from cell B16 words to search for and "A" will be found in lots of cells in column e (eg in E2 "RSM BEARERS"

So, can you clarify better in words exactly what you are trying to do?
And, instead of showing us the results of a formula that is not doing what you want, show us exactly what results you do want from that sample data in columns B & E by entering them manually in column J and posting that with explanation about how you got those results manually.

 

[greevek]

Thanks for updating your profile.


What you have asked is not clear.

Adding to what steve the fish has already said ..
Your current formula, using "*"&B2&"*", is checking for the whole of each column B value within column E values.
For example, "BULLARDS" (cell B6) is found within "Bullards Spirits Ltd" (E6) and hence "BULLARDS" is returned as a result.

"NORFOLK GARDEN MAC" (B10) is first found in "North Norfolk Garden Machinery" (E8) and so "NNGM NFK" (B8) is returned, being the corresponding value to that first match.
Is that what you wanted for "NORFOLK GARDEN MAC" or did you want "NORFOLK GARDEN MAC" returned?

If you want to search for every individual word in column B (eg "Wincer") then I think you will have further problems as Excel will see words as those groups of characters separated by spaces. That would make, for example, both "A" and "W" from cell B16 words to search for and "A" will be found in lots of cells in column e (eg in E2 "RSM BEARERS"

So, can you clarify better in words exactly what you are trying to do?
And, instead of showing us the results of a formula that is not doing what you want, show us exactly what results you do want from that sample data in columns B & E by entering them manually in column J and posting that with explanation about how you got those results manually.

Thank you for coming back to me, I think what I thought might be a quick formula option to highlight the data is turning out to be a much bigger job and more work than I thought. It was supposed to be a data extraction to highlight where the ad identifier contained the full or partial company name as it shouldn't be used in that way but I think at a glance we can see this for ourselves.
Thank you both for your help but I am going to close this thread.