Index function not responding as expected

GAJITCS

Board Regular
Joined
Apr 21, 2015
Messages
66
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)

NOPQRS
3AB103AB10
4AB323AB32
5B42B4
6BA143BA14
7BB73BB7

<tbody>
</tbody>

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


ABCD
1AreaDistrict FromDistrict ToZone
2AB101613
3AB212513
4AB303213
5AB393913
6AB414213
7AB515113
8AL3
9B2
10BA123
11BA3124
12BA13153
13BA16164
14BA20226
15BB163
16BB775
17BB8123

<tbody>
</tbody>

<tbody>
</tbody>
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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Change the formula for Q3 to
=RIGHT(N3,LEN(N3)-O3+1)*1
 
Upvote 0
Fluff, That's spot on. Thank you.

Excuse me for asking, but does FluffSRi mean anything to you?
 
Upvote 0
Glad to help & thanks for the feedback

Excuse me for asking, but does FluffSRi mean anything to you?
Absolutely nothing.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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