Index function not responding as expected

[GAJITCS]

Many thanks for taking the time to stop by and look at this.

Is there an easier way to do this or can this be fixed?


Part of a longer formula is not responding as I expected it to. I suspect it's my expectations that are wrong.

In essence, what I need to achieve is to identify a "Zone" value based on the makeup of a portion of a Post Code.

I have stripped the full postcode down after verifying that it is in a valid format and end up with one or two cells populated with data.

Where the full Post Code bay have been AB10 3EE, it's the outward portion I'm interested in. This gets pulled apart into two cells, one showing "AB", the other showing "10".

To do this, I have the following formulas in cells P3 and Q3

P3= =LEFT(N3,O3 -1)
Q3= =RIGHT(N3,LEN(N3)-O3+1)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AB10[/TD]
[TD]3[/TD]
[TD]AB[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AB32[/TD]
[TD]3[/TD]
[TD]AB[/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B4[/TD]
[TD]2[/TD]
[TD]B[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]BA14[/TD]
[TD]3[/TD]
[TD]BA[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]BB7[/TD]
[TD]3[/TD]
[TD]BB[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The cells in column R need to pick out the value in from column D, where P2 is found in column A and on the same row, the value in Q10 falls between the values in columns B and C inclusive (10 is within the range of 10-16).

The formula I have for this reads:
=IFERROR(INDEX(Post_Code_District_Zone,MATCH(1,INDEX((Post_Code_Area=$P$3)*($Q$3>=Post_Code_District_Start)*($Q$3<=Post_Code_District_End),0),0)),"")

Post_Code_District_Zone = D2:D17
Post_Code_Area = A2:A17
Post_Code_District_Start = B2:B17
Post_Code_District_End = C2:C17


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Area[/TD]
[TD]District From[/TD]
[TD]District To[/TD]
[TD]Zone[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AB[/TD]
[TD]10[/TD]
[TD]16[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AB[/TD]
[TD]21[/TD]
[TD]25[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AB[/TD]
[TD]30[/TD]
[TD]32[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AB[/TD]
[TD]39[/TD]
[TD]39[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AB[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AB[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]BA[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]BA[/TD]
[TD]3[/TD]
[TD]12[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]BA[/TD]
[TD]13[/TD]
[TD]15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]BA[/TD]
[TD]16[/TD]
[TD]16[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]BA[/TD]
[TD]20[/TD]
[TD]22[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]BB[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]BB[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]BB[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 370"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Under testing, if I replace the formula in Q3 with the value it computes to (10), my long formula returns the value 13 which is correct.

What is wrong?

 

[Fluff]

Change the formula for Q3 to
=RIGHT(N3,LEN(N3)-O3+1)*1

 

[GAJITCS]

Fluff, That's spot on. Thank you.

Excuse me for asking, but does FluffSRi mean anything to you?

 

[Fluff]

Glad to help & thanks for the feedback

Excuse me for asking, but does FluffSRi mean anything to you?

Absolutely nothing.