different values in cell (formula too long)

[gullit]

Hi, i have a problem that i can't see the solution to it.
I have 22 different values that a cell may have. i need a formula where if "cell value = "X" then "xx", the problem is that since a have 22 different possible values, the formula becomes too long and get a message "formula too long". I have past the maximun number of characters allowed
example:
cell value="y" then "X"
"L" then "XXX"
"U" then "XXXX", etc.
How can i work around it?
Any advice will be greatly appreciated.

 

[Smitty]

Welcome to the Board!

Check out VLOOKUP.

HTH,

 

[villy]

If you will use VBA code then you can use Select Case Statement..

like...

Select Case cell.value
case "y"
cell1.value="X"
case "L"
cell1.value="XXX"
.
.
.
case else
cell.value= "whatever"
End select

 

[Smitty]

If you will use VBA code then you can use Select Case Statement..

Heya Villy,

I've got to respectfully disagree with you on this. While a Select Case statement would certainly work, it's also a very static approach, so if there are ever any changes or additions to the conditions, then the Select Case statment needs to be adjusted. This is much more time consuming than a Lookup table. Another point would be not to use VBA if a native solution will suffice, because VBA will almost always be slower.

My 2 cents anyway.

 

[gullit]

Thank you very much, i will try both suggestions, but i believe VLOOKUP will do.. i was trying the IF formula.....duh!!...sometimes when
u see the problem from the outside the answer is right in your face. Thanks a lot for the quick help. This forum rocks!!