INDEX / MATCH based on first 3 OR 4 characters

[drewberts]

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?

 

[Special-K99]

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).

 

[drewberts]

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!

 

[drewberts]

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

 

[drewberts]

Ive gone with using IFERROR function to deal with the #VALUE error

 

[Fluff]

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

Confirmed with CSE