Restricting an INDEX MATCH to a specific number of characters RIGHT from the last character

[dougmarkham]

Hi Folks,

I have a simple problem: my INDEX MATCH formula matches 'the first 3 characters RIGHT from the last character' of postcode data in one column; matching it with, any 'postcode data' in another column---this formula returns zone information.

I have the following named ranges:

• Table 1, Col A = PostCodes
• Table 2, Col A = FedExPostcodes
• Table 2, Col C = FedExZones

Here is the formula I've tried and got working...

=INDEX([COLOR=#008000][B]FedExZones[/B][/COLOR],MATCH(RIGHT([B][COLOR=#b22222]PostCodes[/COLOR][/B],[B]3[/B])&"*",[COLOR=#0000ff][B]FedExPostCodes[/B][/COLOR],[B]0[/B]))

I need to modify this formula so it INDEX MATCHES 'the first 3 characters RIGHT from the last character' of postcode data in one column with 'the first 3 characters RIGHT from the last character' of postcode data in another column---this would then return only zone information for exact postcode matches

Here is the same formula I've tried to modify to achieve a like for like match---this formula returns the error message #VALUE .

=INDEX([COLOR=#008000][B]FedExZones[/B][/COLOR],MATCH(RIGHT([B][COLOR=#b22222]PostCodes[/COLOR][/B],3)&"*",RIGHT([COLOR=#0000ff][B]FedExPostCodes[/B][/COLOR],3),0))

I've outlined the situation below
Any help would be greatly appreciated!

Kind regards,

Doug.




Situation: I have two worksheets -

• FIRST: address data including postcodes (UK zip codes),
• SECOND: listed FedEx postcodes and FedEx zone information.

Aim: I wish to do an INDEX MATCH (in Table 1, Col C) to compare postcodes from table 1 and table 2, and return the correct FedEx zone.

• WkSht 1 = Address book [contains Table 1]
• WkSht 2 = FedEx Postcodes/Zones [contains Table 2]

WkSht 1; Table 1: My Address book
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Col A (Postcodes)[/TD]
[TD]Col B (Character Length [=Len(A2:A5)])[/TD]
[TD]Col C (lookup of FedExZone)[/TD]
[/TR]
[TR]
[TD]FK1 (A2)[/TD]
[TD]3[/TD]
[TD]DB (C2)[/TD]
[/TR]
[TR]
[TD]FK2[/TD]
[TD]3[/TD]
[TD]DB[/TD]
[/TR]
[TR]
[TD]FK3[/TD]
[TD]3[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]FK5[/TD]
[TD]3[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]







WkSht 2; Table 2: FedEx Postcode/Zone table
[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A (FedEx Postcodes)[/TD]
[TD]Col B (Postcode Character Length [=Len(A2:A4)][/TD]
[TD]Col C (FedEx zone)[/TD]
[/TR]
[TR]
[TD]FK19 (A2)[/TD]
[TD]4[/TD]
[TD]DB[/TD]
[/TR]
[TR]
[TD]FK20[/TD]
[TD]4[/TD]
[TD]DB[/TD]
[/TR]
[TR]
[TD]FK21[/TD]
[TD]4[/TD]
[TD]DB[/TD]
[/TR]
</tbody>[/TABLE]

I have managed to do this with the following formula:

=INDEX([COLOR=#008000][B]FedExZones[/B][/COLOR],MATCH(RIGHT([B][COLOR=#b22222]PostCodes[/COLOR][/B],[B]3[/B])&"*",[COLOR=#0000ff][B]FedExPostCodes[/B][/COLOR],[B]0[/B]))

What I think my formula stipulates is:

• exactly match the RIGHT 3 characters of the values in the named range PostCodes with the named range FedExPostCodes and return data from the named range FedExZones.

In this situation, the zone information in Table 1 cell C2 is returning zone DB when I need it to return a non-match i.e., #N/A.

The issue:
The formula works except when...

• the Table 2 postcodes have 4 characters (eg., FK19 [cell A2]) and...
• the Table 1 postcodes have 3 matching characters e.g., FK1 [cell A2] within the 4 characters (eg., FK19 [cell A2]) .

How would I modify this formula to say the following???

• exactly match the RIGHT 3 characters of the values in the named range PostCodes with the RIGYHT 3 characters of the values in the named range FedExPostCodes and return data from the named range FedExZones

I tried this and it returned #VALUE error messages.

=INDEX([COLOR=#008000][B]FedExZones[/B][/COLOR],MATCH(RIGHT([B][COLOR=#b22222]PostCodes[/COLOR][/B],3)&"*",RIGHT([COLOR=#0000ff][B]FedExPostCodes[/B][/COLOR],3),0))

 

[Special-K99]

16 people have read this so far and you haven't got one reply.
I think you've overcomplicated the problem by pasting a very lengthy description, that's the sort of thing that puts people off.

Put simply,
What's your input and what output are you trying to get, example data please.

Why isn't this just a simple INDEX(...MATCH(...)) ?

 

[kvsrinivasamurthy]

=INDEX(FedExZones,MATCH(RIGHT(PostCodes,3)&"*",RIGHT(FedExPostCodes,3),0))
Try the same formula as ARRAY formula.
How to enter ARRAY formula.

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.
=INDEX(FedExZones,MATCH(RIGHT(PostCodes,3)&"*",RIGHT(FedExPostCodes,3),0))