Vlookup postcodes

[dixon1983]

Hi,
I have a Vlookup formula which looks up a large postcode list.

The Vlookup looks up the postcode entered (only needs to be in the format YO12, SW1, TS23 for e.g) and returns a value 3 columns along from that postcode.

THe problem I am having is that it will return the same value for all of the TS20's as it looks up TS2 and not TS23 or TS25 (SW1 and not SW15 or SW16). Is there a way to get it to lookup both numbers and not just the first one??

 

[Yogi Anand]

Hi dixon:

Some clarification is needed ... I suggest you post your sample data, then show:

1) what is the Lookup Value

2) what is the LookedUp field

3) the formula you used

4) the result you got

5) and what you think is the correct result

and then let us take it from there!

 

[dixon1983]

(Column A)CA1 (Column C)Newcastle (Col D)Lookup value (COL E)Formula
CA2 Newcastle
CA3 Newcastle
CA4 Newcastle
CA5 Newcastle
CA6 Newcastle
CA7 Newcastle
CA8 Newcastle
CA9 Newcastle
CA10 Stockton
CA11 Stockton
CA12 Stockton
CA13 Stockton
CA14 Stockton
CA15 Stockton
CA16 Stockton
CA17 Stockton
CA18 Stockton
CA19 Stockton
CA20 Stockton
CA21 Stockton
CA22 Stockton
CA23 Stockton
CA24 Stockton
CA25 Stockton
CA26 Stockton
CA27 Stockton
CA28 Stockton
YO1 Leeds
YO2 Unknown
YO3 Unknown
YO4 Unknown
YO5 Unknown
YO6 Stockton
YO7 Stockton
YO8 Leeds
YO9 Unknown
YO10 Leeds
YO11 Hull
YO17 Hull
YO18 Stockton
YO19 Leeds
YO20 Unknown
YO21 Stockton
YO22 Stockton
YO23 Leeds
YO24 Leeds
YO25 Hull
YO26 Leeds
YO27 Unknown
YO29 Unknown
YO30 Leeds
YO32 Leeds
YO33 Unknown
YO40 Unknown
YO41 Hull
YO44 Hull
YO45 Unknown
YO50 Unknown
YO51 Leeds
YO52 Unknown
YO59 Unknown
YO60 Hull
YO61 Stockton
YO62 Stockton
YO63 Unknown


Formula in cell E1 = If(D1="","",VLOOKUP(D1,A1:C100,3))

If I enter CA1 in cell D1 I get Newcastle returned which is correct.
If I enter CA4 in cell D1 I get stockton returned which is incorrect. Should be Newcastle
If I enter CA12 in cell D1 i get Newcastle returned which is incorrect. Should be Stockton

Also if I enter YO6 or YO7 or YO8 i get "unknown" which is incorrect. Should be Stockton, Stockton and Leeds respectively.

Thanks for any help

 

[bosshogg]

try

= IF(D1="","",VLOOKUP(D1,$A$1:$C$100,3,FALSE))

The range is not really sorted, so I would look for an exact match using FALSE...

 

[dixon1983]

Thanks, i'll give that a try.

 

[Yogi Anand]

Hi dixon:

Sorry! ... from what you have posted, it is still not clear how your data is laid out, what is your Lookup value, which is the Lookedup Field, so I suggest, you post only 5 to 10 rows of your actual data using HTML maker, and then let us take it from there!

 

[Aladin Akyurek]

[...]

Formula in cell E1 = If(D1="","",VLOOKUP(D1,A1:C100,3))

If I enter CA1 in cell D1 I get Newcastle returned which is correct.
If I enter CA4 in cell D1 I get stockton returned which is incorrect. Should be Newcastle
If I enter CA12 in cell D1 i get Newcastle returned which is incorrect. Should be Stockton

Also if I enter YO6 or YO7 or YO8 i get "unknown" which is incorrect. Should be Stockton, Stockton and Leeds respectively...

Sort A:C on A in ascending order, add a special record at the top like this...

Book15
	A	B	C	D
1
2	CA1		Newcastle
3	CA10		Stockton
4	CA11		Stockton
5	CA12		Stockton
6	CA13		Stockton
7	CA14		Stockton
8	CA15		Stockton
9	CA16		Stockton
Sheet1

A1:

=CHAR(1)

C1:

=""

and invoke:

=IF(LOOKUP(D1,A:A)=D1,LOOKUP(D1,A:A,C:C),"")