Forum Discussion

Camperslaolie's avatar
Camperslaolie
Copper Contributor
Dec 05, 2023

Help with a formula ChatGPT can't get correct either

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!

  • mathetes's avatar
    mathetes
    Silver Contributor

    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.

    • Camperslaolie's avatar
      Camperslaolie
      Copper Contributor

      mathetes 

       

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

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    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")
    )

     

    • Camperslaolie's avatar
      Camperslaolie
      Copper Contributor
      thanks L.z. ... Duplicates indeed are incorrect.
      I will check once more and try again 🙂

Resources