Matching with variable identifier (kind of)

[PC_Excel]

Hi all,

I'm struggling to work out a formula, and wondering if you guys can help...

I'm wanting to match a postcode with the first part of a postcode so I can assign a code to it, probably using MATCH or VLOOKUP formulas, so ideally the data would look like this

Postcode Postcode prefix Region code
SW1Y6LR SW1Y 6 SW01
SW21QW SW2 1 SW02
SW22FB SW2 2 SW02
SW23JH SW2 3 SW02
SW24KM SW2 4 SW02
SW25SC SW2 5 SW02
SW31AZ SW3 1 SW03
SW32QN SW3 2 SW03

The data comes in two lots (postcodes and then the postcode prefix and region code data), so I'm wanting to find a formula that matches the postcode up with the postcode prefix, then allowing me to assign a region code to each postcode.

The challenge is the postcode prefix varies in length and is a mix letters and numbers

Does anyone know a solution to this please?

Thanks for your help,

PC

[alansidman]

Please read the yellow banner at the top of this page on how to attach a file.

[PC_Excel]

No problem Alan, I've attached a file.

[bebo021999]

In D2 then drag down:

Please Login or Register  to view this content.

The rest can be done with VLOOKUP

[PC_Excel]

Hi Bebo, this is great, thank you! One more thing though, when I try to expand the table (the real set has about 10,000 rows) as below (only thing changed highlighted bold):

=LOOKUP(2,1/COUNTIF(C2,SUBSTITUTE('Region codes'!$A$2:$A$10000," ","")&"*"),'Region codes'!$A$2:$A$10000)

It returns a 0 result, do you know why this is and how to fix?

Thank you,
PC

[PC_Excel]

Hi Bebo, this is great, thank you! One more thing though, when I try to expand the table (the real set has about 10,000 rows) as below (only thing changed highlighted bold):

=LOOKUP(2,1/COUNTIF(C2,SUBSTITUTE('Region codes'!$A$2:$A$10000," ","")&"*"),'Region codes'!$A$2:$A$10000)

It returns a 0 result, do you know why this is and how to fix?

Thank you,
PC

[PC_Excel]

When I change the below (highlighted) section of the formula, it returns a 0 - I don't understand why

=LOOKUP(2,1/COUNTIF(C2,SUBSTITUTE('Region codes'!$A$2:$A$25," ","")&"*"),'Region codes'!$A$2:$A$25)

[bebo021999]

Attach sample again with unmatching code, I guess some region codes are not in same format