Cross referencing data from 2 different sets of data

migIoM

New Member
Joined
Aug 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
image showing issue on row 25.png
image showing issue on row 25.png

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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel

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

varios 24ago2020.xlsm
ABCD
1do not use19 MayisturkeyNot Found
2do not use25 windUnited statesNot Found
3Abhain shalchain, Perth, Scotland, UK4 ventsFranceNot Found
4Achairn, wick England, UKAberanderScotlandScotland
5A'Chruach, Scotland, UKAberdeen bayScotlandScotland
6Aegir, offshore, USAAblaincourtScotlandScotland
7ABS cablingGermany
8AchairnScotland
9A'ChruachScotland
10AegirScotland
Hoja4
Cell Formulas
RangeFormula
D1:D6D1=IFERROR(VLOOKUP(LEFT(A1,FIND(",",A1&",")-1),B:C,2,0),"Not Found")
 

migIoM

New Member
Joined
Aug 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Annotation 2020-08-25 082706.png


1598340550533.png
 

Attachments

  • 1598340384233.png
    1598340384233.png
    161.5 KB · Views: 0

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
ABCD
1do not use19 MayisturkeyNot Found
2do not use25 windUnited statesNot Found
3Abhain shalchain, Perth, Scotland, UK4 ventsFranceScotland
4Achairn, wick England, UKAberanderScotlandScotland
5A'Chruach, Scotland, UKAberdeen bayScotlandScotland
6Aegir, offshore, USAAblaincourtScotlandScotland
7ABS cablingGermany
8AchairnScotland
9A'ChruachScotland
10AegirScotland
11AbhainScotland
Hoja4
Cell Formulas
RangeFormula
D1:D6D1=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

New Member
Joined
Aug 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

migIoM

New Member
Joined
Aug 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
your last solution doesn't work for record 130 below. It should find the match in record 1412 (second screenshot) and return Finland

1598364183857.png


1598364364633.png
 

Attachments

  • 1598364123275.png
    1598364123275.png
    5.6 KB · Views: 0

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
ABCD
1do not use19 MayisturkeyNot Found
2do not use25 windUnited statesNot Found
3Abhain shalchain, Perth, Scotland, UK4 ventsFranceScotland
4Achairn, wick England, UKAberanderScotlandScotland
5A'Chruach, Scotland, UKAberdeen bayScotlandScotland
6Aegir, offshore, USAAblaincourtScotlandScotland
7Kuolavaara-Keulakkopaa, KittilaABS cablingGermanyFinland
8AchairnScotland
9A'ChruachScotland
10AegirScotland
11AbhainScotland
12Kuolavaara-Keulakkopään, KittilaFinland
13
Hoja4
Cell Formulas
RangeFormula
D1:D7D1=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")))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,645
Messages
5,524,054
Members
409,557
Latest member
Excelinho

This Week's Hot Topics

Top