filling other cells based on a postcode?

[Dayveez]

A1---------- A2------------- A3
Postcode--- County-------- Country

<table style="width: 342px; height: 49px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5961;width:122pt" width="163"> <col style="mso-width-source:userset;mso-width-alt:9106;width:187pt" width="249"> </colgroup><tbody><tr style="height:21.0pt" height="28"> <td class="xl63" style="height:21.0pt;width:48pt" width="64" height="28">BT32
</td> <td class="xl63" style="width:122pt" width="163"> County Down
</td> <td class="xl63" style="width:187pt" width="249"> England
</td> </tr> <tr style="height:21.0pt" height="28"> <td class="xl63" style="height:21.0pt" height="28">BT33</td> <td class="xl63"> ?</td> <td class="xl63"> ?
</td> </tr> <tr style="height:21.0pt" height="28"> <td class="xl63" style="height:21.0pt" height="28">BT34
</td> <td class="xl63"> County Down
</td> <td class="xl63"> England
</td> </tr> </tbody></table>
I have a database of UK postcodes on a excel sheet with all the counties and countries next to them.
I want to put in a postcode in any cell for example: BT33, and want it to automatically tell me next to it in 'A2' that its 'county down' and in 'A3' that its 'England'.
..So that i dont have to keep typing 'county down' and 'england' every time someone calls me from that area and i input their details. Instead want to just type BT33 and it comes up.

Please help.

 

[Smitty]

Welcome to the Board!

See this tutorial on VLOOKUP: http://www.contextures.com/xlFunctions02.html

HTH,

 

[Oeldere]

Excel Workbook
	A	B	C
1	Postcode--- County-------- Country
2
3	BT32	County Down	England
4	BT33	?	?
5	BT34	County Down	England
6
7
8	bt32	County Down	England
9	bt33	?	?
10	bt34	County Down	England
Blad2

Cell Formulas
Range	Formula
B8	=VLOOKUP($A8,$A$3:$C$5,COLUMN(),0)
B9	=VLOOKUP($A9,$A$3:$C$5,COLUMN(),0)
B10	=VLOOKUP($A10,$A$3:$C$5,COLUMN(),0)
C8	=VLOOKUP($A8,$A$3:$C$5,COLUMN(),0)
C9	=VLOOKUP($A9,$A$3:$C$5,COLUMN(),0)
C10	=VLOOKUP($A10,$A$3:$C$5,COLUMN(),0)

 

[Dayveez]

thats amazing how i got quick replies. thank you. it looks complicated but i'll try and study hard. a step by step process wouldn't go a miss either as vlookup is a weak spot for me.

 

[Oeldere]

I use in B8 de formula column().

With that formula it's easy to copy the formula to another cells without changing the formula.

In B8 is column() = 2 (B is the second column).

In C8 is column() = 3 (C is the thirth column).

and so on.

 

[Dayveez]

based on my 1st post what i said. is there technical name for it?

example:
I look at this website: http://www.contextures.com/xlFunctions02.html

There is lots of different vlookup very similar, and not sure which one solves my problem. :/

Do you know which one solves my problem?

 

[Oeldere]

I used this one.

Create an Excel VLookup formula


You can also use F1 (= help) in Excel.

 

[Dayveez]

Sorry, I tried the way you did it but nothing happens if i put BT33 on a random cell in the A column.

 

[Smitty]

Sorry, I tried the way you did it but nothing happens if i put BT33 on a random cell in the A column.

No matter where you place the BT33 you need to make sure that you have one of the VLOOKUP's referencing it.

 

[Oeldere]

I try to explain in words.

b8

=VLOOKUP($A8,$A$3:$C$5,COLUMN(),0)

1) Lookup A8 => this is BT32

2) Look this up in the range A3:C5.

3) IF BT32 is found in de Range A3:A5

4) Then look in the 2th colum in the Range (in this example B3)
=> is the explaination of column().

5) BT32 needs to be found exactly.

Do you have questions on this item, just ask.