Excel - which formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-3326009%22%20slang%3D%22en-US%22%3EExcel%20-%20which%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3326009%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20insert%20a%20formula%20in%20a%20spreadsheet%20that%20will%20do%20the%20following%3B%3C%2FP%3E%3CP%3EWhen%20specific%20text%20codes%20are%20entered%20in%20a%20cell%2C%20the%20formula%20will%20refer%20to%20a%20table%20of%20codes%20and%20attribute%20a%20value%20which%20will%20be%20entered%20into%20the%20cell.%20example%3B%3C%2FP%3E%3CP%3EI%20type%20TL%20in%20the%20cell%20and%20the%20value%20I%20want%20that%20to%20return%20in%20the%20cell%20is%201%3C%2FP%3E%3CP%3EThere%20are%20further%20codes%20that%20need%20a%20value%20of%201%20returning%2C%20and%20there%20are%20further%20codes%20that%20have%20higher%20values%20from%202%20to%205.%3C%2FP%3E%3CP%3EWhich%20formula%20do%20I%20use%3F%26nbsp%3B%20How%20do%20I%20phrase%20the%20formula%20in%20excel%3F%26nbsp%3B%20Do%20I%20need%20to%20put%20the%20text%20codes%20into%20a%20table%20for%20look%20up%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3326009%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3326249%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20which%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3326249%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1382150%22%20target%3D%22_blank%22%3E%40JayC1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(OR(B2%3D%22TL%22%2CB2%3D%22UA%22%2CB2%3D%22ER%22)%2C1%2CIF(OR(B2%3D%22WW%22%2CB2%3D%22QS%22%2CB2%3D%22GH%22)%2C2%2CIF(OR(B2%3D%22UI%22%2CB2%3D%22OP%22%2CB2%3D%22KL%22)%2C3%2C%22no%20match%22)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20a%20nested%20IF%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3327274%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20-%20which%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3327274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%2C%20I%20will%20give%20this%20a%20try.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I want to insert a formula in a spreadsheet that will do the following;

When specific text codes are entered in a cell, the formula will refer to a table of codes and attribute a value which will be entered into the cell. example;

I type TL in the cell and the value I want that to return in the cell is 1

There are further codes that need a value of 1 returning, and there are further codes that have higher values from 2 to 5.

Which formula do I use?  How do I phrase the formula in excel?  Do I need to put the text codes into a table for look up?

2 Replies

@JayC1 

=IF(OR(B2="TL",B2="UA",B2="ER"),1,IF(OR(B2="WW",B2="QS",B2="GH"),2,IF(OR(B2="UI",B2="OP",B2="KL"),3,"no match")))

Maybe with a nested IF formula.

@Quadruple_Pawn 

Thank you so much, I will give this a try.