IF Not Working

[ajocius]

I am using Excel 2010. For some reason the attached file example does not work. Can someone far more brighter than I resolve my issue. Bottom line, if I enter one of 17 possible characters X,0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F I would like to get a 4 character string back. Why do I get the #VALUE error? I spent the better part of a day trying to figure this out. I even used the Hex2Bin function within the IF statement and that created other problems. Any assistance would help an over worked engineer out. Thanks.

Tony

[Tony Valko]

You're trying to add (sum) text values.

See if this helps:

http://contextures.com/xlFunctions02.html

[djapigo]

Be careful with 1 vs. "1"... one is a number, the other is text. Plus, I think you have too many IFs... I think Excel has a limit...

Anyway, I suggest you use a vlookup instead. See my attachment...

Hope this helps...

[ajocius]

Thank you gentlemen for making a simple problem into a simple answer. I will use the lookup table as suggested. Thanks to you both.

Tony

[Tony Valko]

You're welcome. We appreciate the feedback!

[AlKey]

Or this:

=IF(A1="X","XXXX",IFERROR(LOOKUP(A1,{1,2,3,4,5,6,7,8,9},{1,10,100,101,110,111,1000,1001}),IFERROR(LOOKUP(A1,{7,8,9},{111,1000,1001}),IFERROR(LOOKUP(A1,{"A","B","C"},{1010,1011,1100}),IFERROR(LOOKUP(A1,{"D","E","F"},{1101,1110,1111}),"")))))