If number starts with X, do Y.

Named Ranges:

Ranges with multiple cells, each containing a specific prefix.
GREATNUMS
OKNUMS

Ranges consisting of a single cell, with a single number in it.
GREATVALUE
OKVALUE
OTHER


Column A consists of long numbers - one in each cell. I want Excel to see if those numbers start with specific prefixes and assign a value depending on what prefix it is.

Requirements:
If the number starts with any value listed in the GREATNUMS range, the formula spits out the number in the GREATVALUE cell.
If the number starts with any value listed in the OKNUMS range, the formula spits out the number in the OKVALUE cell.
If the number does not start with any prefixes in the GREATNUMS or OKNUMS ranges, the formula spits out the number in the OTHER cell.

(The formula would have to account for the fact that the prefixes are different sizes - some are two digits, and some are three digits)


I have tried doing this with a lot of IF-LOOKUP-SEARCH statements, but the formula is beginning to be EXTREMELY long (like close to 1,000 characters), which is ridiculous for such a simple operation. There's gotta be a better way. ??

(EDIT: I have attached an example spreadsheet in comment #3.)

Thanks!

Please post a sample sheet - Thx

In B2

=IF(SUMPRODUCT(--(LEFT(A2,LEN(greatnums))*1=greatnums)),greatvalue,IF(SUMPRODUCT(--(LEFT(A2,LEN(oknums))*1=oknums)),okvalue,othervalue))