VLookup Simple but wont work

[babypink2807]

I have been at this all day, and embarrased about it and so frustrated. All I want is for the Vlookup to pull through a street address on another sheet. To start with the formula was showing as text, wold not show the value. Then for whatever reason my spreadsheet kept changing from Letter Column Headings to Numbers, dont know why it just did. I then found that I could not copy and paste from the original spreadsheet to a brand new one, again dont ask me why. I closed everything down and back up again. managed to coy and paste my data in to a brand new spreadsheet. My formula still will not pull through the street address. Please can someone help me. All day I have been trying and I cant type in 2750 street addresses, its just too much

Please help

Book2.xlsx
	A	B	C	D	E	F
1	Account ID No	Street	First Name	Surname	Company Name	Building Name
2	ACT10	#N/A	Andrew	Vaughan	W A & C M Vaughan	High Dovecote
3	ACT1000	#N/A	James	Blanchard	Blanchard Enterprises	New House Farm
4	ACT1003	#N/A	David	Leigh	Birchwood Park Farms	Birchwood Park Farm
5	ACT1004	#N/A	B	Mycock	Birchwood Park Farms	Birchwood Park Farm
6	ACT1005	#N/A	Gene	Fisher	Best Grass Ltd	Fishers Farm
7	ACT1006	#N/A	R & J	Dymond	Battledown Contractors	Battledown Farm
8	ACT1007	#N/A	Caroline	Bateman	Bateman Rural Associates
9	ACT1008	#N/A	Toby	Bateman	Bateman Rural Associates
10	ACT1010	#N/A	Robert	Ballie	Ballie Haylage	Ivy House
11	ACT1014	#N/A	Brian	Jones	B T Jones	Fields Farm
12	ACT1016	#N/A	Rob	Singleton	B J Singleton (Alresford)	Ranscombe Farm
13	ACT1017	#N/A	Lawrence	Rich	B G & L D Rich	Stud Farm
14	ACT1019	#N/A	Mark	Edwards	B C & D J Edwards & Son	Pool Farm
15	ACT1020	#N/A	Joe	Lopez	B B Grounds Care Ltd	Frieze Hall Farm
16	ACT1021	#N/A	Brian	Scott	B & B Scott	Odmoston Farm
17	ACT1022	#N/A	Sarah	Brown	Avoncrook Farmers	Avoncrook Cottage
18	ACT1024	#N/A	Richard	Burrough	Ashbridge Countryside Services	Manor Farm
19	ACT1025	#N/A	John	Scott	Artemis Lane & Agriculture Ltd	Crouchlands
20	ACT1026	#N/A	Owen	Hutchinson	Arrowcruit Limited	White House Farm
21	ACT1027	#N/A	Paul	Apps	Apps Grab Hire	The Bungalow
22	ACT1029	#N/A	Andy	Ball	Andy Ball Contractors	Hensbury Farm
23	ACT1031	#N/A	Andrew	Baillie	Andrew Baillie & Son	Blackdub Farm
24	ACT10324	#N/A	Stewart	Sinclair	Stewart Sinclair
25	ACT1035	#N/A	Allen	James	Allen James Hay & Straw	Wessex Farm
26	ACT1036	#N/A	Allan	Wilson	Allan W J Wilson
27	ACT1039	#N/A	Simon	Corner	Aequus Land Ltd	Malt House
28	ACT104	#N/A	William	Stratton	Stratton Contracting	Siabost Tillygreig
29	ACT1040	#N/A	John	Adams	Adams Farm Ltd
30	ACT1042	#N/A	Alun	Glyn Jones	Acton Farming Company	Meadowend Farm
31	ACT1043	#N/A	Andrew	Smith	A W Smith & Sons	Home Farm
32	ACT1046	#N/A	A	Dunn	A T & A Dunn	Newham Barton Farm
33	ACT1047	#N/A	Ben	Coles	A S Coles & Son	Park Farm
Sheet1

Cell Formulas
Range	Formula
B2:B33	B2	=VLOOKUP(A2,Sheet2!A2:S2757,2,FALSE)

 

[babypink2807]

This is the sheet containing the street address

Book2.xlsx
	A	B	C	D
1	ID No	Street	Company	Contact
2	ACT10		W A & C M Vaughan	Andrew Vaughan
3	ACT100	Sidmouth Road	Stuart Partners Ltd	Ross Stuart
4	ACT1000		Blanchard Enterprises	James Blanchard
5	ACT1001		Blaiket Mains Dairy Company Limited	Rodger Callander
6	ACT1002	Bleasdale Lane	B L & E A Clark & Son	B L Clark
7	ACT1003	Roston Common	Birchwood Park Farms	David Leigh
8	ACT10033			Padfield & Dyke
9	ACT1004	Roston Common	Birchwood Park Farms	B Mycock
10	ACT1005	Old Chapel Rd	Best Grass Ltd	Gene Fisher
11	ACT1006		Battledown Contractors	R & J Dymond
12	ACT1007	2 Webble Green	Bateman Rural Associates	Caroline Bateman
13	ACT1008	2 Webble Green	Bateman Rural Associates	Toby Bateman
14	ACT1009		Barnacre Alpacas	Paul Rippon
15	ACT101	Sidmouth Road	Stuart Partners Ltd	Harry Bayliss
16	ACT1010		Ballie Haylage	Robert H Ballie
17	ACT1011		B Watt & Sons	Robert Watt
18	ACT1012		B W S Verrill	Baden Verrill
19	ACT1013		B V & S M Johnson	Susan Mary Johnson
20	ACT1014	Betchton Road	B T Jones	Brian Jones
21	ACT1015	North Kyme Fen	B Nicholson & Son	Andrew Nicholson
22	ACT1016	Barnetts Wood Lane	B J Singleton (Alresford)	Rob Singleton
23	ACT1017	Blaisdon Lane	B G & L D Rich	Lawrence Rich
24	ACT1018		B D & D G Andrew	Derrick Andrew
25	ACT1019		B C & D J Edwards & Son	Mark Edwards
26	ACT102	Sidmouth Road	Stuart Partners Ltd	Troy Stuart
27	ACT1020	Coxtie Green Road	B B Grounds Care Ltd	Joe Lopez
28	ACT1021		B & B Scott	Brian Scott
Sheet2

 

[Fluff]

All the values in col A of the formula sheet have a line feed after the text, hence you get #N/A errors.
Either get rid of the line feeds or use

=VLOOKUP(CLEAN(A2),Sheet2!A$2:B$2757,2,FALSE)

 

[Anonymous1378]

All your data in column A of sheet1 have an extra line break. And you aren't using absolute referencing on your lookup range. Try this if you don't want to clean up your data in column A.

Book83
	A	B	C	D	E	F
1	Account ID No	Street	First Name	Surname	Company Name	Building Name
2	ACT10		Andrew	Vaughan	W A & C M Vaughan	High Dovecote
3	ACT1000		James	Blanchard	Blanchard Enterprises	New House Farm
4	ACT1003	Roston Common	David	Leigh	Birchwood Park Farms	Birchwood Park Farm
5	ACT1004	Roston Common	B	Mycock	Birchwood Park Farms	Birchwood Park Farm
6	ACT1005	Old Chapel Rd	Gene	Fisher	Best Grass Ltd	Fishers Farm
7	ACT1006		R & J	Dymond	Battledown Contractors	Battledown Farm
8	ACT1007	2 Webble Green	Caroline	Bateman	Bateman Rural Associates
9	ACT1008	2 Webble Green	Toby	Bateman	Bateman Rural Associates
10	ACT1010		Robert	Ballie	Ballie Haylage	Ivy House
11	ACT1014	Betchton Road	Brian	Jones	B T Jones	Fields Farm
12	ACT1016	Barnetts Wood Lane	Rob	Singleton	B J Singleton (Alresford)	Ranscombe Farm
13	ACT1017	Blaisdon Lane	Lawrence	Rich	B G & L D Rich	Stud Farm
14	ACT1019		Mark	Edwards	B C & D J Edwards & Son	Pool Farm
15	ACT1020	Coxtie Green Road	Joe	Lopez	B B Grounds Care Ltd	Frieze Hall Farm
Sheet1

Cell Formulas
Range	Formula
B2:B15	B2	=IFNA(VLOOKUP(CLEAN(A2),Sheet2!$A$2:$S$2757&"",2,FALSE),"")

 

[babypink2807]

=IFNA(VLOOKUP(CLEAN(A2),Sheet2!$A$2:$S$2757&"",2,FALSE),"")

Hi

I have no idea what a line feed is never heard of it, I have added the formula you have mentioned above and ow it shows #VALUE! where NA was

 

[babypink2807]

This is what happens now when I added the formula

 

[Anonymous1378]

Perhaps the ampersand is causing that to be recognized as an array; try this instead

=IFNA(VLOOKUP(CLEAN(A2),Sheet2!$A$2:$S$2757,2,FALSE),"")

it's probably going to return a bunch of zeroes though

 

[babypink2807]

Perhaps the ampersand is causing that to be recognized as an array; try this instead

=IFNA(VLOOKUP(CLEAN(A2),Sheet2!$A$2:$S$2757,2,FALSE),"")

it's probably going to return a bunch of zeroes though

Its all blank, nothing is in my column. All that data is text, and I think I may believe that a link break is a hidden "return", there isnt one. I copied and pasted both sets of data from the old spreadsheet in to a brand new one and "values" when I pasted, so it is just raw data. Honestly its driving me nuts

 

[Anonymous1378]

Try posting an XL2BB of your 2 sheets again? I suppose your calculation options under the formula tab is set to automatic?

 

[babypink2807]

Sheet one is the sheet I would like to pull the address through to. Ihave removed formulas and the column is now blank. To enter my formulas I use the Equal sign and then follow the automatice guidance

Book2.xlsx
	A	B	C	D	E	F	G
1	Account ID No	Street	First Name	Surname	Company Name	Building Name	Village
2	ACT10		Andrew	Vaughan	W A & C M Vaughan	High Dovecote	Walton
3	ACT1000		James	Blanchard	Blanchard Enterprises	New House Farm	Southmoor
4	ACT1003		David	Leigh	Birchwood Park Farms	Birchwood Park Farm	Roston
5	ACT1004		B	Mycock	Birchwood Park Farms	Birchwood Park Farm	Roston
6	ACT1005		Gene	Fisher	Best Grass Ltd	Fishers Farm	Freethorpe
7	ACT1006		R & J	Dymond	Battledown Contractors	Battledown Farm	Shebbear
8	ACT1007		Caroline	Bateman	Bateman Rural Associates		Membury
9	ACT1008		Toby	Bateman	Bateman Rural Associates		Membury
10	ACT1010		Robert	Ballie	Ballie Haylage	Ivy House	Kirmond Le Mire
11	ACT1014		Brian	Jones	B T Jones	Fields Farm	Betchton
12	ACT1016		Rob	Singleton	B J Singleton (Alresford)	Ranscombe Farm	Bighton
13	ACT1017		Lawrence	Rich	B G & L D Rich	Stud Farm	Blaisdon
14	ACT1019		Mark	Edwards	B C & D J Edwards & Son	Pool Farm	Melverley
15	ACT1020		Joe	Lopez	B B Grounds Care Ltd	Frieze Hall Farm	South Weald
16	ACT1021		Brian	Scott	B & B Scott	Odmoston Farm
17	ACT1022		Sarah	Brown	Avoncrook Farmers	Avoncrook Cottage
18	ACT1024		Richard	Burrough	Ashbridge Countryside Services	Manor Farm	Damerham
19	ACT1025		John	Scott	Artemis Lane & Agriculture Ltd	Crouchlands	Kirdford
20	ACT1026		Owen	Hutchinson	Arrowcruit Limited	White House Farm
21	ACT1027		Paul	Apps	Apps Grab Hire	The Bungalow	Horne
22	ACT1029		Andy	Ball	Andy Ball Contractors	Hensbury Farm	Bere Ferrers
23	ACT1031		Andrew	Baillie	Andrew Baillie & Son	Blackdub Farm
24	ACT10324		Stewart	Sinclair	Stewart Sinclair		Firth
25	ACT1035		Allen	James	Allen James Hay & Straw	Wessex Farm	Sherborne Causeway
26	ACT1036		Allan	Wilson	Allan W J Wilson		Fearn
27	ACT1039		Simon	Corner	Aequus Land Ltd	Malt House	Longnor
28	ACT104		William	Stratton	Stratton Contracting	Siabost Tillygreig	Udny
29	ACT1040		John	Adams	Adams Farm Ltd		Cloughmills
30	ACT1042		Alun	Glyn Jones	Acton Farming Company	Meadowend Farm	Acton
31	ACT1043		Andrew	Smith	A W Smith & Sons	Home Farm	Withcall
32	ACT1046		A	Dunn	A T & A Dunn	Newham Barton Farm	Newham
33	ACT1047		Ben	Coles	A S Coles & Son	Park Farm	Bloxham
34	ACT1048		A S	Jackson	A S & K M Jackson	Ripton House Farm	Langleydale
35	ACT1052		Andrew	Oxtoby	A Oxtoby & Sons	Park End Farm	Bishop Burton
36	ACT1057		Callum	Derby	A M Davies & Co	Blackpark Farm
37	ACT1058		A M	Davies	A M Davies & Co	Blackpark Farm
Sheet1