Help with a formula ChatGPT can't get correct either

[Camperslaolie]

Hi there.

I hope someone can help me, because after trying 20 times myself and asking ChatGPT for help, I still do not have the correct answer.

Ok, here goes:

A postal code consisting of 5 digits is mentioned in cel H124.
The first two digits in cel H124 correspond to possibilities that are divided in 5 possible zones.
These 5 zones are as follows:
Zone 1 consists out of : 33, 34, 41 to 48, 49, 50, 52, 53, 57 and 59.
Zone 2 consists out of: 54 to 73, 75, 76, 91, 93 and 94.
Zone 3 consists out of: 72, 77, 78, 79, 82 to 89.

Zone 4 consists out of: 01 to 08, 14, 15, 31, 38, 39, 66, 99.

Zone 5 consists out of: 16 to 19, 23 to 32, 49, 75.

I need to have the formula for Cel R124 so it will say which zone the postal code mentioned in cel H124 falls under. 

Everybody, thank you for your help!

[#2]

@Camperslaolie 

I hope someone can help me, because after trying 20 times myself and asking ChatGPT for help, I still do not have the correct answer.

That's probably because ChatGPT didn't understand the question you were asking. The fact is that there are probably multiple ways to accomplish the task. There often are in Excel several different ways to get from point A to point B. In this case, I've taken a very visual approach, and created a table of the first two digits in one column and the corresponding zone in the second.  Then used INDEX and MATCH to find the zone that corresponds to those first two digits.

I did not take the trouble to fill in all of the spaces in column B--I'll leave that to you. But you should be aware before doing it that your directions are somewhat inconsistent. Take just the first two rows as an example:

Zone 1 consists out of : 33, 34, 41 to 48, 49, 50, 52, 53, 57 and 59.
Zone 2 consists out of: 54 to 73, 75, 76, 91, 93 and 94.

Where actually do 57 and 59 belong?

There are more such inconsistencies, so be alert to them.

[#3]

Hi @Camperslaolie 

It'll difficult/inaccurate with a bunch of duplicates:

When above issue is fixed:

- Name B2:Z5 as (i.e.) Zones

in A10

=LET(
  rw, SUM( ISNUMBER( SEARCH(LEFT(B9,2),Zones) ) * SEQUENCE(ROWS(Zones)) ),
  IF(rw, "Zone " & rw, "Not found")
)

[#4]

@mathetes 

Thank you for the notification of the duplicates... that indeed is incorrect.
I will check once more and try again 🙂

[#5]

thanks L.z. ... Duplicates indeed are incorrect.
I will check once more and try again 🙂