populating one cell (selecting from drop-down options) based on content of another cell in sheet

Guy Price

Hi there, first question posted on the Smartsheet Community, here we go...

I'm trying to work out a formula/solution for automatically populating a cell (from a drop-down containing 5 geographic location options) based on the first three characters of a postcode (zip code) in another column (approx 150 postcodes).

For example, in the screenshot here, T23 is a Munster postcode, so I need to adjacent cell to select Munster from the drop-down.

My best option so far is a cumbersome IF THEN formula that includes all 150 postcodes, but I'm sure there must be a far more efficient way of doing it.

I have looked at so many formulas here int he community that my brain is now fully scrambled! Really appreciate any help and advice, thank you!

Paul Newcome

You would need to create a table that lists out all 150 postcodes in one column and their corresponding regions in another. From there you can use either a VLOOKUP or (the much preferred) INDEX/MATCH.

Guy Price

This is the best I can do so far...

=IF([What is the first part of your postcode/eir code?]@row = "T23", "Munster", IF([What is the first part of your postcode/eir code?]@row = "R95", "Munster", IF([What is the first part of your postcode/eir code?]@row = "D6", "Leinster")))

...and it's working...

...but it's going to take me days to do all 150-odd postcode/locator permutations!

I'm sure this is one for a combination of VLOOKUP MATCH INDEX CONTAINS etc but it's beyond me at this stage!

Paul Newcome

You would need to create a table that lists out all 150 postcodes in one column and their corresponding regions in another. From there you can use either a VLOOKUP or (the much preferred) INDEX/MATCH.

Guy Price

Many thanks @Paul Newcome that did the job 😀✔️ (combined with this short INDEX/MATCH video https://www.google.com/search?q=smartsheet+how+to+use+INDEX+MATCH&rlz=1C5CHFA_enGB970GB971&biw=1692&bih=884&ei=HCRpYq6BPPOEhbIPm9maqA4&ved=0ahUKEwiu_6yajbT3AhVzQkEAHZusBuUQ4dUDCA4&uact=5&oq=smartsheet+how+to+use+INDEX+MATCH&gs_lcp=Cgdnd3Mtd2l6EAMyBggAEBYQHjoFCAAQkQI6BQgAEIAEOgcIIRAKEKABSgQIQRgASgQIRhgAUABY4E9gwFNoA3ABeACAAZABiAGTEJIBBDIxLjSYAQCgAQHAAQE&sclient=gws-wiz#kpvalbx=_Bz1pYorGIdLygQa0-YD4Ag18)