Cross referencing data from 2 different sets of data

M

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
 
Sort by date Sort by votes
Thank you for your help
Unfortunately there still some issues with the formula. Please see image below.
It finds Burton link to England in Record 40 but when it search for Burton again in record 41 it doesn't allocate England again as it should, instead it returns Not Found, as highlighted in red

1598384610255.png


there are a few Burton in the green data base but all allocated to England as shown in screenshot below.

1598384657835.png
 

Attachments

  • 1598384458665.png
    1598384458665.png
    11.7 KB · Views: 4
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Let's keep adjusting the formula.
It would help a lot if you put your data samples with the XL2BB tool (see my signature), that way, I could copy the data. From an image you can't.

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
8Burton Pidsea, HullAchairnScotlandEngland
9Burton Wold Wind Farm, KetteringA'ChruachScotlandEngland
10AegirScotland
11AbhainScotland
12Kuolavaara-Keulakkopään, KittilaFinland
13Burton PidseaEngland
14Burton Wold Northern ExtensionEngland
15Burton Wold SouthEngland
16
Hoja4
Cell Formulas
RangeFormula
D1:D9D1=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")))
 
Upvote 0
test
Sample MM.xlsx
ABC
1DO NOT USE19 MayisTurkey
2DO NOT USE251 WindUnited States
3Abhainn Shalachain, Perth, Scotland, UK4 VentsFrance
4Achairn, Wick, England, UKAberarderScotland
5A'Chruach, Kilmichael Forest, Mid-Argyll, Scotland, UKAberdeen BayScotland
6Aegir, Offshore, USAAberdeen Harbour Floating PontoonScotland
7Ness Point, Lowestoft, England, UKAberedw HillWales
8Aikengall, Innerwick, East Lothian, Scotland, UKAberystwyth UniversityWales
9Alfa SiteAblaincourtFrance
10Allt a Chonais, Caithness, Sutherland and Ross-shire, Scotland, UKAblainzevelleFrance
11Andershaw, South Lanarkshire, Scotland, UKABS Cabling Auditing (Portfolio)Germany
12Andover, Hampshire, England, UKAchairnScotland
13Aneby, Jönköping, SwedenAcharn Biomass FacilityScotland
14Ardlawhill Wood, New Aberdour, Aberdeenshire, Scotland, UKAcharoleScotland
15Ark Hill, Glenogilvy, Forfar, Angus, Scotland, UKAchery - MayotFrance
16Armistead, Gatebeck, Kendal, Cumbria, England, UKAchiet Le GrandFrance
17Arnish Moor, Offshore, Southwest of Stornoway, Western IslesAchlachanScotland
18Ashby de la Zouch, Ashby, Leicestershire, England, UKA'ChruachScotland
19Auchenhay, Castle Douglas, Dumfries and Galloway, Scotland, UKAcresIreland
20Ava, Near Aspeå, Västerbotten Co, SwedenAdanaTurkey
21Loch Garbad, Kildonan, Scotland, UKAderstedt IGermany
22Avonmouth, Bristol, England, UKAderstedt IIGermany
23Bagenalstown, Co. Carlow, IrelandAdriersFrance
24Ballagh Wind Farm, approx 10KM S Newcastle West, Co. Limerick, IRELANDAegirScotland
25Ballygawley, Northern Ireland, UKAftonScotland
26Bank End Rig Wind Farm, Drumclog, South Lanarkshire Scotland, UKAfton ValleyScotland
27Barranafaddock, County Waterford, Munster, IrelandAGAFrance
28Beam Hill, Donegal, IrelandAgua ClaraDominican Republic
29Bellacorrick, County Mayo, IrelandAguilcourtFrance
30Beltonean Mountain, Co. Tyrone, Nothern Ireland, UKAikengallScotland
31Benhar Wind Farm, Harthill, North Lanarkshire, Scotland, UKAikengall IIScotland
32Biggleswade Wind Farm, Biggleswade, Bedfordshire, UKAillianvilleFrance
33Blackcraig Wind Farm, Blackcraig Hill, Dumfries and Galloway, Scotland, UKAirds BayScotland
34Blekhem, SwedenAirfield Farm (Podington)England
35Boardinghouse, Cambridgeshire, England, UKAirie Cottage Water WellScotland
36BontoftaAiriesScotland
37Bord Na Mona, IrelandAirriequhillartScotland
38BroughnessAkitaJapan
39Burbo Bank, Offshore, Burbo Flats, Liverpool Bay, Irish SeaAKO P13.2 projet solaire de la CRE - Services? Project Name?France
40Burton Pidsea, Hull, England, UKAkyar RESTurkey
41Burton Wold Wind Farm, Kettering, Northamptonshire, England, UKAladinSweden
42Calder Water, South Lanarkshire, Scotland, UKAlaizSpain
43Cappawhite wind Farm, Co. Tipperary, IrelandAlaskaEngland
44Carno, Powys, Wales, UKAlbainzevelleFrance
45Carrickallen Wind Farm, Co.Cavan, EireAlbineFrance
46Carscreugh Wind Farm, Glenluce, Dumfries and Galloway, Scotland, UKAldermyrbergetSweden
47Big Field Wind Farm (previously known as Project Cedar), South of Bude, Cornwall, England, UKAlderneuk FisheryScotland
48Cefn CroesAliağa RESTurkey
49Centocor & DePuy, Co. Cork, IrelandAlibunarSerbia
50Childersburg Pellet Plant, USAAlibunar A+BSerbia
51Clashindarroch, Nr Huntley, Aberdeenshire, Scotland, UKAliteUnited States
52Clogh Mills & KnockavannonAlle CattUnited States
53Cloghan, Co. Offaly, IrelandAllouéFrance
54Cloghinarney, Co. Antrim, Northern Ireland, UKAllt DeargScotland
55Codling Bank Wind Park, Offshore East Coast of Ireland (Wicklow)Allt FionnScotland
56Coire Na Cloiche Wind Farm, Alness, Easter Ross, UKAllt MheilScotland
57Collipulli, Chile, South America, USAAllt RubhaScotland
58Corriemoillie, Garve, Scottish Highlands, UKAlparslan RESTurkey
59Cour, Sheirdrim Hill, Argyll and Bute, Scotland, UKAlpineFrance
60DO NOT USEAlta (Mojave) III/IV/VUnited States
61Cretshengan Hydro, Tarbert, Argyll, Scotland, UKAltagowlanIreland
62Cruach an Lochain, Scotland, UKAltagowlanNorthern Ireland
63Crystal Rig Wind Farm, Nr Dunbar, East Lothian, Scotland, UKAltdourie House (Invercauld Estate)Scotland
64Curraghbinny, Co. Cork, IrelandAltercannochScotland
65Dalnessie Wind Farm, Sutherland, Scottish Highlands, UKAltonaUnited States
66Derrydarroch Hydro, Crianlarich, Stirling, Scotland, UKAlwens ForestWales
67Derwydd Bach, Denbighshire, North Wales, UKAmadeusUnited States
68DevroAmasya RES (Kayadüzü)Turkey
69DiazAmec Water WellsEngland
70Doldaheia, Vindafjord, NorwayAmerican Falls IUnited States
71Glanaruddery, previously known as Dromadda More, Co. Kerry, IRELANDAmerican Falls IIUnited States
72DO NOT USEAMI Tidal ArayFrance
73Dunbeg Wind Farm, Co. Londonderry, Northern Ireland, UKAmourès BouissacFrance
74Dunmaglass Wind Farm, Dunmaglass Estate, Inverness, Scotland, UKAmundsenHungary
75Earlseat Wind Farm, Between Leven and Kirkcaldy, Fife, Scotland, UKAn CnocIreland
76Earlshaugh, Falkirk, Scotland, UKAn SuidheScotland
77Eastern Cape and TeesbusAncudChile
78Eber Urla YamactepeÅndbergSweden
79Edinbane, Balmeanach, Struan, Isle of Skye, ScotlandAndershawScotland
80EldoretÄngersjökölenSweden
81EyeAngervilleFrance
82Fäbodliden, Vasterbotten, SWEDENAnguilcourtFrance
83FalesrassaAnnaghbegIreland
84Faughary Wind Farm, Co Leitrim, IRELANDAnnaghmoreIreland
85FolkloreAnnelles le NitisFrance
86Forest MillÅnstadblåheiaNorway
87ForthAntelope RidgeUnited States
88Fraisthorpe Wind Farm, Bridlington, York, England, UKAntezant la ChapelleFrance
89Galsworthy Wind Farm, North Devon, Southwest England, UKAntilhueChile
90Gettnabo Wind Farm, Torsas, SwedenAntoignéFrance
91Gilja Wind Farm, Gjesdal, Rogaland county, NorwayAntrimUnited States
92Glen Lethnot, Water of Saughs and Burn of Duskintry, near Glen Lethnot, Angus, Scotland, UKAppin ForestScotland
93Glencarbry Windfarm, Co Tipperary, IrelandApple BlossomUnited States
94GlencassleyAquind InterconnectorEngland
95Golan HeightsAragonne MesaUnited States
96Gortmonly Hill Wind Farm, Strabane, County Tyrone, Northern Ireland, UKAraucoChile
97Gravdal Wind Farm, Municipality of Vestvågøy, NorwayArbignyFrance
98Greengairs Wind Farm, North Lanarkshire, Scotland, UKArboussolsFrance
99Griffin and Clyde, Elvanfoot, South Lanarkshire, Scotland, UKArbuckle MountainUnited States
100GumbölebergetArc-en-Thiérache (Sains-Richaumont)France
101GunnilstorpArcy-sur-CureFrance
102Hall Wind Farm, Leven, East Riding of Yorkshire, England UKArderrooIreland
103Hazlehead Wind Farm, Barnsley West Yorkshire, England, UKArdkinglas (Clachan Flats)Scotland
104Hill of Fechel Wind Farm, Udny, Aberdeenshire, Scotland, UKArdlawhill WoodScotland
105Hill of FernyhirstArdoch and Over EnochScotland
106Hill of FiddesArdouvalFrance
107Hillhead of Auquhirie Wind Farm, Southwest of Stonehaven Aberdeenshire, Scotland, UKArdtaraigScotland
108HollyfordArecleochScotland
109HopefieldArk HillScotland
110Huai Bong Wind Farm, Dan Khun district, Nakhon Ratchasima province, ThailandArkonaGermany
111Humber GatewayArmadillo FlatsUnited States
112HyndburnArmisteadEngland
113Inner SoundArmistead Ext.England
114Inverclyde, Greenock, Renfrewshire, Scotland, UKArmorgreenFrance
115DO NOT USEArnish MoorScotland
116DO NOT USEArnsheenScotland
117Islay Tidal, Inner Hebrides Islands, Scotland, UKArque et SegurFrance
118DO NOT USEArribaUnited States
119Joukhaisselkä Wind Park, Sodanklä, FINLANDArrouaiseFrance
120Kalvatnaan, Nordland, NorwayArsac 2France
121Keith Hill, East Lothian, Scotland, UKArsac 5France
122DO NOT USEASC PortfolioUnknown
123Kiaby Wind FarmAseletSweden
124Killymasney, Donegal, IrelandAseri (Aseriaru)Estonia
125KintarbertAshfieldEngland
126KnockardgurAshmark HillScotland
127Knockendurrick Wind Farm, Glengap, nr Gatehouse of Fleet, Dumfries & Galloway, Scotland, UKAshtabula IIIUnited States
128KnostropAspa BrukSweden
129KotkaAssacFrance
130Kuolavaara-Keulakkopaa, Kittila, FinlandAssel ValleyScotland
131Kype Muir, Strathaven, South Lanarkshire, Scotland, UKAstideyUruguay
132La BoerreAston ClintonEngland
133Lairdmannoch Wind Farm, near Ringford, Dumfries & Galloway, Scotland, UKAstoria 1United States
134Lamb HillAstoria 2United States
135LatamäkiAtheaIreland
136Leitir Guingaid & Leitir PeicAtrebatieFrance
137Lendrick MuirAttleboroUnited States
138DO NOT USEAubandUnited States
139ListaAuchenhayScotland
140Little Raith Wind Farm, Cowdenbeath, Fife, Scotland, UKAuchenlongfordScotland
141Little TernAuchrobertScotland
142LochluichartAuchtygillsScotland
143LorgAunis MoindreauxFrance
144Loughderryduff Wind Farm, Maas Townland, Co Donegal, IRELANDAuroraChile
145Low SpinneyAurora County WindUnited States
146LowestoftAutremencourt ExtensionFrance
147LumicityAutremencourt-F3France
148Lurganboy Wind Farm, Lurganboy, Co. Donegal, IRELANDAuwahiUnited States
149MaclachriestonAuxerroisFrance
150Macritch HillAuxois-SudFrance
151MantonAuxois-Sud IIFrance
152MaverickAvaSweden
153Menai StraitAvailles Thouarsais IraisFrance
154Merthyr TydfilAvaladesPortugal
155Mid Hill Wind Farm, Fetteresso Forest, Aberdeenshire, Scotland, UKAvelFrance
156MiddlewickAviemore Primary SchoolScotland
157Millour HillAvloiGreece
158MindorkAvoineFrance
159MinscaAvonmouth (Bristol) PortEngland
160Moel MaelogenAvonmouth (Bristol) Port ExtensionEngland
161Moor House Wind Farm, Sadberge, Nr Darlington, Co. Durham, England, UKAvrilléFrance
162MORLAws OceanUnknown
163Mosjøen Wind Farm, Nordland, NORWAYAyr HospitalsScotland
164Mount PleasantAyrshireUnited States
165Mountain WatersAyvalık-I RESTurkey
166DO NOT USEAzat le risFrance
167Mynydd BwllfaAzérablesFrance
168Mynydd Portref Wind Farm, Rhondda Cyon Taf, Wales, UKAzurFrance
169Mynydd y GwrhydBaalonFrance
170Nant y MochBachan BurnScotland
171Neilstonside & Nether CarswellBad a CheoScotland
172Newton of FortrieBagaltiTurkey
173NHS Grampian HospitalBagasse Congregation AssetsFrance
174North BrackenridgeBaillieScotland
175North PerryBaird NorthUnited States
176North Pickenham Wind Farm, Nr Swaffham, Norfolk, England, UKBaja CaliforniaMexico
177Northumberland National ParkBajouveFrance
178NutberryBake FarmEngland
179NutsgroveBalabanlı RESTurkey
180OakfieldBalcons du LomontFrance
181Old Mill, Co. Monaghan, IrelandBaldoon AirfieldScotland
182OrielBaldwinUnited States
183OwenninyBalıkesir RESTurkey
184Oyster BayBalko WindUnited States
185Pant y Wal Wind Farm, Glynogwr, Bridgend, Wales, UKBallaghIreland
186Pates HillBalliemeanochScotland
187Paul's Hill, near Elgin, Morayshire, Scotland, UKBallinlaIreland
188Pen y CymoeddBalloughterIreland
189Pentland RoadBallybayIreland
190Philippine TidalBallybeaghIreland
191PisanoBallycaddenIreland
192Point & SandwickBallycumberIreland
193PriestgillBallyfasyIreland
194RabelofBallyfrenisNorthern Ireland
195Race BankBallygilbertNorthern Ireland
196RaftsjoBallyhanedinNorthern Ireland
197RagnallBallyhouraIreland
198RaheenlaghBallylongfordIreland
199RampionBallyMartinIreland
200RansonmoorBallymartin II (Smithstown)Ireland
201Ballymena (Michelin)Northern Ireland
202BallynancoranIreland
203BallypriorIreland
204BallywaterIreland
205BalnacoilScotland
206BalquhandyScotland
207BambersEngland
208Bambers ExtEngland
209Bank of New York (Portfolio)United States
210Bankend RigScotland
211Banks (Portfolio)Scotland
212BaresTurkey
213BarharrowScotland
214Barlockhart MoorScotland
215BarmollochScotland
216BarmoorEngland
217Barn FarmEngland
218BarnadivaneIreland
219BarnbackleScotland
220BarnesmoreIreland
221Barns of AyreScotland
222BarnstorfGermany
223Baron WindsUnited States
224BaronvilleFrance
225Barony CollegeScotland
226BarranafaddockIreland
227Barrel LawScotland
228Barren RidgeUnited States
229BarrowEngland
230Barrow BluewayIreland
231Barton CloseEngland
232Barwhillantry EstateScotland
233Basse Thièrache SudFrance
234Bat Smart Curtailment SystemUnknown
235BatillyFrance
236BaudignécourtFrance
237Bay StateUnited States
238BaybridgeIreland
239BayWa (Portfolio)No information
240BayWa 3 (Portfolio)England
241Bazoches-les-GallerandesFrance
242BeaconUnited States
243Beacon HillScotland
244Beale HillIreland
245Beale Hill I & IIIreland
246Beam HillIreland
247BeatriceScotland
248BeauchampsFrance
249Beauly of Keith OHLScotland
250BeaumontFrance
251BeausoleilFrance
252Beck BurnEngland
253Beinn an Tuirc Phase 3Scotland
254Beinn GhrideagScotland
255Beinn MhorScotland
256BeinneunScotland
257Bel BenChile
258Bel-AirFrance
259Belen RESTurkey
260BellacFrance
261BellacorrickIreland
262BellavistaChile
263Belle FontaineFrance
264BelleekIreland
265BellefeuilleCanada
266BelleuseFrance
267BellshillScotland
268BelwindBelgium
269BemersydeCanada
270Ben AketilScotland
271Ben BrackScotland
272Ben CrokaigScotland
273Ben GlasScotland
274Ben Sca /UK Sites PortfolioScotland
275BenassayFrance
276BenbrackScotland
277BenchlandsCanada
278Bendooragh RoadNorthern Ireland
279BénetFrance
280Bénet IIFrance
281BenharScotland
282Bennadrove LandfillScotland
283Benquhat HillScotland
284BenshinnieScotland
285BerdumGermany
286BergvikSweden
287Berkshire EastUnited States
288BermonvilleFrance
289Bermuda PVBermuda
290Berry BurnScotland
291BeşpınarTurkey
292BethelUnited States
293Bewick DriftEngland
294BezirciTurkey
295Bicker FenEngland
296BictonEngland
297BieslesFrance
298Big BlueUnited States
299Big FieldEngland
300Big LevelUnited States
301Big SkyUnited States
302Big SpringUnited States
303Big ZlatanSweden
304BiggleswadeEngland
305Billezois/Saint ChristopheFrance
306BilsthorpeEngland
307Binn Eco ParkScotland
308BioLumicity (Portfolio)MULTIPLE SITES
309Biomass Boiler Multiple Investigations (25-30)England
310Biomass CHPEngland
311Biomass CHP / Distric HeatingScotland
312Biomass District Heating SchemeScotland
313Biomass Electricity 5MWScotland
314Biomass Oat Husk Plant 5MWScotland
315Biomass Plant 49MWEngland
316BiozatFrance
317BirkebergetSweden
318BishopthorpeEngland
319Bitter RidgeUnited States
320BjörkvaknettSweden
321BjørnstigfjelletNorway
322Black LawScotland
323Black Law Extension Phase IScotland
324Black Law Extension Phase IIScotland
325Black OakUnited States
326Black Oak GettyUnited States
327Black Stockarton MoorScotland
328BlackcraigScotland
329Blackhillock SubstationScotland
330Blackjack RidgeUnited States
331BlacklawScotland
332Blackspring RidgeCanada
333Blackstone EdgeEngland
334BlackwaterScotland
335BladenboroUnited States
336Blaengwawr FarmWales
337Blakeley Hill FarmEngland
338BlaklidenSweden
339BlanlessFrance
340Blantyre Muir I & IIScotland
341Blary HillScotland
342BlekhemSweden
343BlissUnited States
344Block IslandUnited States
345BlombayFrance
346BLOSTFrance
347Blue Canyon IUnited States
348Blue Canyon IIUnited States
349Blue Canyon VUnited States
350Blue Canyon VIUnited States
351Blue Energy - 3 NI Wind farmsNorthern Ireland
352Blue NortherUnited States
353Blue Summit IIUnited States
354Blue Summit IIIUnited States
355Blueberry HillsUnited States
356BluestoneUnited States
357Bluff PointUnited States
358BlythEngland
359Blyth DemonstratorEngland
360BoardinghouseEngland
361Boat of GartenScotland
362BocaddonEngland
363Böğürtlen RESTurkey
364BohultSweden
365Boiling SpringsUnited States
366BoinFrance
367Bois BallayFrance
368Bois BigotFrance
369Bois BouchéFrance
370Bois ChardonFrance
371Bois ClergeonsFrance
372Bois d’Anchat (Binas)France
373Bois de BajouveFrance
374Bois de CholletzFrance
375Bois de FollevilleFrance
376Bois de GrisanFrance
377Bois de l’ArcheFrance
378Bois de Morval (Hesdin)France
379Bois GuillaumeFrance
380Bois JoliFrance
381Bois LouisFrance
382BondrumSweden
383Bonneuil-les-EauxFrance
384BonnevalFrance
385BoolardIreland
386BooltiaghIreland
387Booltiagh II & IIIIreland
388Bord Gais DD Portfolio (13 Sites)Unknown
389Borkum Riffgrund 1Germany
390BottonnettEngland
391BougueneufFrance
392BouillancourtFrance
393Boundary LaneEngland
394BouresseFrance
395Bourgogne IFrance
396Bourgogne II - TonnerroisFrance
397BoussayFrance
398BowbeatScotland
399Bowbeat ExtensionScotland
400BoyndieScotland
401BozokTurkey
402BracklinIreland
403Brady IUnited States
404Brady IIUnited States
405Braes O' DouneScotland
406BrannbergetSweden
407BrännlidenSweden
408BrantleyUnited States
409Brantt-KullsjolidSweden
410Breagho RoadNorthern Ireland
411Brechfa Forest WestWales
412BréhatFrance
413BrenigWales
414Brie ChampenoiseFrance
415BrignolesFrance
416Brillac - Oradour FanaisFrance
417BriscoeUnited States
418Bristol PortEngland
419British ColumbiaCanada
420Broad MountainUnited States
421BroadleaAustralia
422BröcklingebergetSweden
423Brockloch Rig IScotland
424Brockloch Rig IIIScotland
425Brockloch Rig WindScotland
426Broken CrossScotland
427BroomhillEngland
428BrothertonScotland
429BrotorpSweden
430Brough Head Wave FarmScotland
431Brough Ness Tidal FarmScotland
432BrownieleysScotland
433BroxtedEngland
434BruckanaTipperary
435Brule CountyUnited States
436Brusque la BarraqueFrance
437Brusque SégalasseFrance
438Bruyère GrandeFrance
439Bryn BlaenWales
440Bryn BlaenWales
441Bryn Y FedwenWales
442BuccleuchScotland
443Buck MountainUnited States
444BuckeyeUnited States
445BuckleberryUnited States
446BuckthornUnited States
447Buffalo TrailCanada
448BuheiiNorway
449Bull RunUnited States
450BunkimaltaIreland
451BunnyconnellanIreland
452Burbo BankEngland
453Burbo Bank ExtensionEngland
454Burghead Wave FarmScotland
455Burn of WhilkScotland
456Burn of Whilk ExtensionScotland
457Burnfoot HillScotland
458Burnhead (Hydro)Scotland
459Burnhead (Wind)Scotland
460Burt CountyUnited States
461Burton PidseaEngland
462Burton WoldEngland
463Burton Wold Northern ExtensionEngland
464Ballymena (Michelin)Northern Ireland
Sheet1
 
Upvote 0
Hi,
Please notice that line 26 is showing that unfortunately the search has to be more complex. Your last formula will return USA for row 26 but this line should be match to Scotland.
Ideally it has to search column A for the first word that will find a match in column B, as discussed. However, it should look FIRST for a matching country in column C
I hope this makes sense
Many thanks
 
Upvote 0
migIoM said:
the search has to be more complex
Click to expand...
You're right, I guess the search is more complex.
I also assume that you will have to go back to your initial formula.

Just comment, on your last example:
In A you have:
"Bank End Rig Wind Farm" with spaces.
In B you have:
"Bankend Rig" without spaces, that cannot be solved by some search formula.

____________________________________________________________________________________________________________
In the following example, you have 4 pieces of data that start with "Hill", if you had in column "B" a piece of information with "Hill", the 4 pieces of information would have the same country and are probably different. It is only an example to highlight.
varios 24ago2020.xlsm
A
104Hill of Fechel Wind Farm, Udny, Aberdeenshire, Scotland, UK
105Hill of Fernyhirst
106Hill of Fiddes
107Hillhead of Auquhirie Wind Farm, Southwest of Stonehaven Aberdeenshire, Scotland, UK
Hoja4


The above is probably what happens with your question from post # 1, with the country of France, but I can't be sure, because in the data you put in I don't see the complete relation of column B.

If it's not a problem, maybe you could share your book in the cloud such as dropbox or googledrive.
 
Upvote 0
Hi Dante,
Unfortunately I am not comfortable sharing the whole set of data as it is my employer's data.
I do appreciate all your help with this Excel conundrum though.
 
Upvote 0
Do not worry.
That happens with name searches. You will have to consider all the patterns: spaces, without spaces, with "ä" without "a", with hyphen, and so on.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
INDEX MATCH from two sheets onto third sheet?
Replies
1
Views
93
etaf
etaf
P
  • Question
How to use a MATCH helper column containing rows from a different sheet and INDEX in an array formula to return based on data in the helper column?
Replies
0
Views
166
purekarting
P
DNapoli
  • Solved
Index and Match Excel Question.
2 3
Replies
24
Views
442
Peter_SSs
Peter_SSs
szita2000
  • Question
Index Match Match from a named table to a range
Replies
1
Views
309
Alex Blakenburg
A
S
  • Solved
Problem modifying INDEX formula
Replies
1
Views
172
MountainFog
M

Forum statistics

Threads
1,214,927
Messages
6,122,309
Members
449,080
Latest member
jmsotelo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top