Forum Discussion
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!
- mathetesSilver Contributor
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.
- CamperslaolieCopper Contributor
Thank you for the notification of the duplicates... that indeed is incorrect.
I will check once more and try again 🙂
- LorenzoSilver Contributor
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") )
- CamperslaolieCopper Contributorthanks L.z. ... Duplicates indeed are incorrect.
I will check once more and try again 🙂