Formula in Excel not recognizing other named formulas

Hey y'all-

I'm new to this site and was really hoping someone could help me with this one.

I've got an Excel 2003 sheet where column E has a 6-digit code input into it. I'm able to right a function off that code that outputs if E2 (for example) equals 123456 then H2 outputs "Dog," if E2 equals 123457 then H2 outputs "Cat," and so on until I reach the max of 7 nested functions. Problem is, I have a lot more than 7 codes that I need to analyze. This Excel sheet will be used every two weeks, with lots of codes input that need to have what the code means output. I created a named formula called "part1" where:

=if(sheet1!E2=123456,"Dog",if(sheet1!E2=123457,"Cat", etc. It didn't work when I had $ in front of E & 2.

All variables in "part1" work when tested where H2 = part1

I created a "part2" with more variables, checked each and it works too.

My problem now is that when I have H2 set to =if(part1,part1,part2) to test all the variables, it'll work on any variable in "part2" but not in "part1."

Any help would be much appreciated!

Looks like a good use for a UDF
for example

Please Login or Register  to view this content.

Hi new tech guy,

welcome to the forum.

I think you may be better off constructing a table with your numbers in one column and the text in the adjacent column, like this

Please Login or Register  to view this content.

Say this list is 100 rows long ..

Then, when you enter a number in, say C2, you can have this formula in D2 to return the appropriate text

=vlookup(C2,$A$1:$B$100,2,false)

does that help?