INDEX / MATCH based on first 3 OR 4 characters

drewberts

Board Regular
Joined
Sep 5, 2013
Messages
177
Office Version
  1. 365
Platform
  1. Windows
I have a column in a table (called TabPostcodes) with full postcodes and iv'e added a second column where I want to populate with the relevant county based on a separate tables on a different worksheet based on partial postcodes.

e.g. I have several postcodes that are in the same county - CH1 2RJ, CH1 2LK, CH2 5TY, CH48 6YU. These are all in Cheshire.

In my separate table I have the partial postcodes in column A and the corresponding county in column B.

e.g.

CH1 Cheshire
CH2 Cheshire
CH3 Cheshire
...
CH48 Cheshire
etc

For each full postcode I need to return the county based on either the first 3 characters OR the first 4. I've tried the following which I think should return based on the first 4 charcters, but I'm getting an error... (i'm adding this as an array formula).

=INDEX(TabLookup[ColB],MATCH(LEFT([@Postcode],4),TabLookup[County]))

I think my problem is that I need to handle the possibility that the character lookup could be 3 or 4 characters.

Any ideas?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I dont know much about tables so this may not work

Try

=INDEX(TabLookup[ColB],MATCH(LEFT([@Postcode],FIND(" ",[@Postcode])-1),TabLookup[County]))

So find the space in the partial postcode and subtract 1 to give you the length of the postcode to search for
so if space is at 4th character then the partial postcode is 3 characters long, if space is at 5th character then the partial postcode is 4 characters long (always 1 less than where the space is).
 
Last edited:
Upvote 0
Thanks Special-K99
I see your logic but I still get a #REF error. There is also an additional issue which I didn't think important before but now clearly is if we are searching for a " " character. Some of the postcodes I don't know so these cells have N/A in them, i.e. there is no space. I have this linked to a county called "No Postcode" on my second sheet. I don't get a #REF error with these, just a #VALUE error (assuming its because there is no " "

The #REF error suggests ive referenced something wrong rather than your logic not working!
 
Upvote 0
Ok, found the referencing problem and fixed that and your formula works - but only if it finds a " ".
I get a #VALUE error if my "postcode" doesn't have a space.

Appreciate your time
 
Upvote 0
Another option
=INDEX(TabLookup[ColB],MATCH(LEFT([@Postcode],4),left(TabLookup[County]&" ",4),0))

Confirmed with CSE
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top