Need IF Statement to return first two digits when first digit is 0
Hello, I need a formula that will return a city name based on the first two digits of a four-digit code. However, it comes back with a #INVALID COLUMN VALUE error if the first digit is a 0.
For example: 01 = Louisville, 02 = Tampa, 03 = San Antonio, etc.
If the number starts with 1, such as 10, the formula works fine but I just need to know how to include the preceding 0. Adding the preceding 0 in the formula does not work (it deletes it once saved).
My formula in the "TwoDig" column:
=(LEFT([Primary Column]@row, 2))
My formula in the "Campus" column:
=IF(TwoDig@row = 1, "Louisville", IF(TwoDig@row = 2, "Tampa", IF(TwoDig@row = 3, "San Antonio", IF(TwoDig@row = 4, "Cincinnati", IF(TwoDig@row = 5, "Hazard", IF(TwoDig@row = 6, "Miami", IF(TwoDig@row = 7, "Austin", IF(TwoDig@row = 8, "Nashville", IF(TwoDig@row = 9, "Richmond", IF(TwoDig@row = 10, "Sarasota", IF(TwoDig@row = 11, "Myrtle Beach", IF(TwoDig@row = 12, "Gainesville", IF(TwoDig@row = 13, "Asheville", IF(TwoDig@row = 14, "Houston", IF(TwoDig@row = 16, "Dallas", false)))))))))))))))
Thank you!
Best Answer
-
You need to translate the TwoDig into a value, it's technically returning as a text string as you have, not a number so it's not matching 06 = 6. So you can change the formula in your TwoDig column to
=VALUE(LEFT([Primary Column]@row, 2))
or include VALUE in your "Campus" column like
=IF(VALUE(TwoDig@row) = 10, "Sarasota", IF(VALUE(TwoDig@row) = 6, "Miami"))... and so on
But I'd also like to suggest using a lookup table and my favorite formula INDEX/MATCH. You could set it up similar to below, or use a cross-sheet reference with your lookup information.
=INDEX([Lookup Value]:[Lookup Value], MATCH(VALUE(TwoDig@row), [Table # Lookup]:[Table # Lookup], 0))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Answers
-
You need to translate the TwoDig into a value, it's technically returning as a text string as you have, not a number so it's not matching 06 = 6. So you can change the formula in your TwoDig column to
=VALUE(LEFT([Primary Column]@row, 2))
or include VALUE in your "Campus" column like
=IF(VALUE(TwoDig@row) = 10, "Sarasota", IF(VALUE(TwoDig@row) = 6, "Miami"))... and so on
But I'd also like to suggest using a lookup table and my favorite formula INDEX/MATCH. You could set it up similar to below, or use a cross-sheet reference with your lookup information.
=INDEX([Lookup Value]:[Lookup Value], MATCH(VALUE(TwoDig@row), [Table # Lookup]:[Table # Lookup], 0))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
This worked! Also, thank you for the INDEX/MATCH suggestion. I will try this in the future.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!