Cross referencing data from 2 different sets of data

[migIoM]

Hi,

Please see image.I am using the formula below to find matches for column A in Column B and returning column C in column D.

For row 35 I expecting no matches but for some strange reason the formula is bringing France as a match.

=IFERROR(INDEX($C$1:$C$2736,MATCH(1,--NOT(NOT(SEARCH($B$1:$B$2736,A1))),0)),”Not Found”)

Please advise,
Many thanks

 

[DanteAmor]

Hi and welcome to MrExcel

I think this formula is more comfortable. It is a regular formula.

varios 24ago2020.xlsm
	A	B	C	D
1	do not use	19 Mayis	turkey	Not Found
2	do not use	25 wind	United states	Not Found
3	Abhain shalchain, Perth, Scotland, UK	4 vents	France	Not Found
4	Achairn, wick England, UK	Aberander	Scotland	Scotland
5	A'Chruach, Scotland, UK	Aberdeen bay	Scotland	Scotland
6	Aegir, offshore, USA	Ablaincourt	Scotland	Scotland
7		ABS cabling	Germany
8		Achairn	Scotland
9		A'Chruach	Scotland
10		Aegir	Scotland
Hoja4

Cell Formulas
Range	Formula
D1:D6	D1	=IFERROR(VLOOKUP(LEFT(A1,FIND(",",A1&",")-1),B:C,2,0),"Not Found")

 

[migIoM]

Thank you Dante. Unfortunately that formula is not behaving, please see image. Record 24 should find Ballagh in record 281 (second image) returning Scotland in column F.
Furthermore the count of Not Found has gone up to 2,711 of a total of 3,444 records.
Miguel

 

[DanteAmor]

I think you got the wrong image, in the second image it says "Benhar" and we are looking for "Ballagh" ...

So what is the rule to look for?
From this name:
"Ballagh Wind Farm, approx 10KM S Newcastle West",
Search for "Ballagh Wind Farm"
Or just "Ballagh"?
The name before the comma or the name before the space
Or a combination of both:

varios 24ago2020.xlsm
	A	B	C	D
1	do not use	19 Mayis	turkey	Not Found
2	do not use	25 wind	United states	Not Found
3	Abhain shalchain, Perth, Scotland, UK	4 vents	France	Scotland
4	Achairn, wick England, UK	Aberander	Scotland	Scotland
5	A'Chruach, Scotland, UK	Aberdeen bay	Scotland	Scotland
6	Aegir, offshore, USA	Ablaincourt	Scotland	Scotland
7		ABS cabling	Germany
8		Achairn	Scotland
9		A'Chruach	Scotland
10		Aegir	Scotland
11		Abhain	Scotland
Hoja4

Cell Formulas
Range	Formula
D1:D6	D1	=IFERROR(VLOOKUP(LEFT(A1,FIND(",",A1&",")-1),B:C,2,0),IFERROR(VLOOKUP(LEFT(A1,FIND(" ",SUBSTITUTE(A1&" ",","," "))-1),B:C,2,0),"Not Found"))

 

[migIoM]

Hi, sorry you're right, this is the correct screenshot , record 185

searching in the first word should suffice
so only Ballagh in above example
Many thanks
Mig

 

[migIoM]

your last solution doesn't work for record 130 below. It should find the match in record 1412 (second screenshot) and return Finland

 

[DanteAmor]

Well, in the second screen you have "ää" and in the first screen you have "aa" without "¨"
Replace "aa" in the second or "ää" in the first.

 

[migIoM]

is is not possible to look only into the first word before the hyphen ? so the name before the comma, space or hyphen
Many thanks

 

[DanteAmor]

Try this:

=IFERROR(VLOOKUP(LEFT(A1,FIND(",",A1&",")-1),B:C,2,0),IFERROR(VLOOKUP(LEFT(A1,FIND(" ",SUBSTITUTE(A1&" ",","," "))-1),B:C,2,0),IFERROR(VLOOKUP(LEFT(A1,FIND("-",SUBSTITUTE(A1&"-","-"," "))-1),B:C,2,0), "Not Found"))

 

[DanteAmor]

is is not possible to look only into the first word before the hyphen ?

In that case it should be like this:

varios 24ago2020.xlsm
	A	B	C	D
1	do not use	19 Mayis	turkey	Not Found
2	do not use	25 wind	United states	Not Found
3	Abhain shalchain, Perth, Scotland, UK	4 vents	France	Scotland
4	Achairn, wick England, UK	Aberander	Scotland	Scotland
5	A'Chruach, Scotland, UK	Aberdeen bay	Scotland	Scotland
6	Aegir, offshore, USA	Ablaincourt	Scotland	Scotland
7	Kuolavaara-Keulakkopaa, Kittila	ABS cabling	Germany	Finland
8		Achairn	Scotland
9		A'Chruach	Scotland
10		Aegir	Scotland
11		Abhain	Scotland
12		Kuolavaara-Keulakkopään, Kittila	Finland
13
Hoja4

Cell Formulas
Range	Formula
D1:D7	D1	=IFERROR(VLOOKUP(LEFT(A1,FIND(",",A1&",")-1),B:C,2,0), IFERROR(VLOOKUP(LEFT(A1,FIND(" ",SUBSTITUTE(A1&" ",","," "))-1),B:C,2,0), IFERROR(VLOOKUP(LEFT(A1,FIND("-",A1&"-")-1)&"*",B:C,2,0),"Not Found")))