Assign area to postcode from multiple arrays function

[gingerbug]

Hello,

I'm hoping you can help. I have a list of the first 4 letters of a postcode, and based on a separate list of postcodes, I'd like to return a value of what sector they're from. Here is an example:

Sheet One

1 BB1 1 FORMULA TO WORK OUT SECTOR
2 BB5 2 FORMULA TO WORK OUT SECTOR
3 BL1 2 FORMULA TO WORK OUT SECTOR
4 BL2 2 FORMULA TO WORK OUT SECTOR

Sheet Two

1 Bolton Wigan Salford
2 BL1 1 BB1 1 M6 6
3 BL2 2 BB5 2 M6 1
4 BL3 1
5 BL3 6

Obviously, the real data is more complete than that, but hopefully you get the idea. In 'normal english,' I imagine the formula needs to work something like:
IF A1 matches any of the array in sheet 2, A1 - A5, Then return "Bolton, but if it doesn't, try range in Sheet 2, B1-B5 and if it matches, return Wigan. If it doesn't match that, try the next column etc etc, and if it doesn't match any of the columns, then simply return 'No Sector.

I feel this might be asking too much of excel, especially as the formula will be on each row in the main sheet.

Am I asking too much?

Looking forward to your responses,

[Kaper]

Try such array formula *in B1:

Please Login or Register  to view this content.

*...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[gingerbug]

Thanks so much, this does in principal work, however when I tried to adapt the formula to use the real data, it seemed to return 'Bolton' for everything. The reason I adapted the formula is because Sheet 2 actually has 10 columns of data (so, ends at J) and the longest length of the column is 99:

Please Login or Register  to view this content.

Any ideas why it's always returning Bolton for any data?

Thanks so much, this is excellent by the way!

[gingerbug]

I've fixed it! I realised I needed to change the value of A1 in the MAX function!!

Thanks so much for your help

[Kaper]

Glad you noted this, and thanks for the rep and for marking thread as solved.