Need IF Statement to return first two digits when first digit is 0

Kerrie

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!

Jason Tarpinian

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

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

Kerrie

This worked! Also, thank you for the INDEX/MATCH suggestion. I will try this in the future.