Lookup formula

[xxsalahxx]

Hello
I have a spreadsheet that has 6000 postcodes in column A and only 3000 postcodes in column B, and towns in column c, I would like to match up whats in column a and b and if it is a match i would like to add into column D the name of the town. I cannot do this by cell as all of the postcodes are mixed through, any help would be appreciated if there is a formula i can use?

 

[awoohaw]

Im not sure what column you want to lookup to and from, or how many towns are in column c. But, here is a guess at a solution.
And i don't use post codes, I use zip codes, so my guess at to valid postcodes is just random number and text strings.
I'm using 60 and 30 instead of 6000 and 3000, you can scale it up.

Book1
	A	B	C	D
1	PostCode1	Postcode2	Town	Result
2	P61F70	O59P05	Town1	Not Found
3	Y89C57	Z07T42	Town2	Town8
4	X50Y96	O59P05	Town3	Not Found
5	O29N27	F36R94	Town4	Town28
6	B92A83	X20Z72	Town5	Town13
7	K76T76	F36R94	Town6	Town28
8	R72R86	Y37K75	Town7	Not Found
9	Z07T42	F03S76	Town8	Not Found
10	C46N00	J04E35	Town9	Not Found
11	Z05I51	X50Y96	Town10	Town3
12	G02M97	F36R94	Town11	Town28
13	D64Z20	D64Z20	Town12	Town12
14	X20Z72	Y37K75	Town13	Not Found
15	T51M04	V19B68	Town14	Not Found
16	W26U68	R97O51	Town15	Not Found
17	I66M74	D64Z20	Town16	Town12
18	S35M42	E20G73	Town17	Not Found
19	E43M98	C77A92	Town18	Not Found
20	R93Y90	F03S76	Town19	Not Found
21	A33K64	U82F72	Town20	Town23
22	E60M00	V67B61	Town21	Not Found
23	R62U44	N38I51	Town22	Not Found
24	U82F72	A48Y46	Town23	Not Found
25	K60E08	C77A92	Town24	Not Found
26	F46X66	Z65R49	Town25	Not Found
27	O11F59	R93Y90	Town26	Town19
28	Z68H97	F03S76	Town27	Not Found
29	F36R94	X50Y96	Town28	Town3
30	F45O62	O59P05	Town29	Not Found
31	H67E87	I66M74	Town30	Town16
32	E23R50		Town31
33	R97O51		Town32
34	X75O57		Town33
35	U14D22		Town34
36	J88Q19		Town35
37	E37T02		Town36
38	O59P05		Town37
39	F78S92		Town38
40	Z65R49		Town39
41	X50V34		Town40
42	L74V18		Town41
43	F76I96		Town42
44	A48Y46		Town43
45	H36H96		Town44
46	J04E35		Town45
47	V67B61		Town46
48	E00I58		Town47
49	M80U70		Town48
50	Z26F36		Town49
51	E20G73		Town50
52	W48T80		Town51
53	V86U17		Town52
54	V19B68		Town53
55	Y78U04		Town54
56	F03S76		Town55
57	K10N21		Town56
58	N38I51		Town57
59	Y37K75		Town58
60	F09G71		Town59
61	C77A92		Town60
Sheet1

Cell Formulas
Range	Formula
D2:D31	D2	=IFERROR(INDEX($C$2:$C$31,MATCH(B2,$A$2:$A$61,0)),"Not Found")

 

[xxsalahxx]

Thank you for your quick response, so for example the postcode in your list in column B (postcode 2) O59P05 im trying to find out if it is listed in column A (postcode 1) and if it is then i want to enter the town from Column C into Column D. Hope this makes sense. any help is appreciated.

 

[awoohaw]

Thank you for your quick response, so for example the postcode in your list in column B (postcode 2) O59P05 im trying to find out if it is listed in column A (postcode 1) and if it is then i want to enter the town from Column C into Column D. Hope this makes sense. any help is appreciated.

how about this:

Book1
	A	B	C	D
1	PostCode1	Postcode2	Town	Result
2	P61F70	O59P05	Town1	Town37
3	Y89C57	Z07T42	Town2	Town8
4	X50Y96	O59P05	Town3	Town37
5	O29N27	F36R94	Town4	Town28
6	B92A83	X20Z72	Town5	Town13
7	K76T76	F36R94	Town6	Town28
8	R72R86	Y37K75	Town7	Town58
9	Z07T42	F03S76	Town8	Town55
10	C46N00	J04E35	Town9	Town45
11	Z05I51	X50Y96	Town10	Town3
12	G02M97	F36R94	Town11	Town28
13	D64Z20	D64Z20	Town12	Town12
14	X20Z72	Y37K75	Town13	Town58
15	T51M04	V19B68	Town14	Town53
16	W26U68	R97O51	Town15	Town32
17	I66M74	D64Z20	Town16	Town12
18	S35M42	E20G73	Town17	Town50
19	E43M98	C77A92	Town18	Town60
20	R93Y90	F03S76	Town19	Town55
21	A33K64	U82F72	Town20	Town23
22	E60M00	V67B61	Town21	Town46
23	R62U44	N38I51	Town22	Town57
24	U82F72	A48Y46	Town23	Town43
25	K60E08	C77A92	Town24	Town60
26	F46X66	Z65R49	Town25	Town39
27	O11F59	R93Y90	Town26	Town19
28	Z68H97	F03S76	Town27	Town55
29	F36R94	X50Y96	Town28	Town3
30	F45O62	O59P05	Town29	Town37
31	H67E87	I66M74	Town30	Town16
32	E23R50		Town31
33	R97O51		Town32
34	X75O57		Town33
35	U14D22		Town34
36	J88Q19		Town35
37	E37T02		Town36
38	O59P05		Town37
39	F78S92		Town38
40	Z65R49		Town39
41	X50V34		Town40
42	L74V18		Town41
43	F76I96		Town42
44	A48Y46		Town43
45	H36H96		Town44
46	J04E35		Town45
47	V67B61		Town46
48	E00I58		Town47
49	M80U70		Town48
50	Z26F36		Town49
51	E20G73		Town50
52	W48T80		Town51
53	V86U17		Town52
54	V19B68		Town53
55	Y78U04		Town54
56	F03S76		Town55
57	K10N21		Town56
58	N38I51		Town57
59	Y37K75		Town58
60	F09G71		Town59
61	C77A92		Town60
Sheet1

Cell Formulas
Range	Formula
D2:D31	D2	=IFERROR(INDEX($C$2:$C$61,MATCH(B2,$A$2:$A$61,0)),"Not Found")

 

[xxsalahxx]

everything is just coming up as not found, do you think its looking for a match between A1 and B1 where i need it to search the whole of column a? Column D should be the same town as column C

 

[awoohaw]

Check your formulas. (or share yours? a minisheet would be helpful). As you can see the mini sheet is post 4 is working. How about pasting my mini sheet into a fresh worksheet and see if it works.

remember to scale up your formula to 6000?

=IFERROR(INDEX($C$2:$C$6001,MATCH(B2,$A$2:$A$6001,0)),"Not Found")

 

[xxsalahxx]

Fantastic it works, thank you so much its really appreciated.

 

[awoohaw]

Great! I am pleased to help.

Best Wishes!