SOLVED
Home

Help - got a little lost with a formula solution and now confused!

%3CLINGO-SUB%20id%3D%22lingo-sub-562274%22%20slang%3D%22en-US%22%3EHelp%20-%20got%20a%20little%20lost%20with%20a%20formula%20solution%20and%20now%20confused!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562274%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%20am%20sure%20this%20will%20be%20easy%20for%20the%20community%20but%20your%20help%20would%20be%20a%20life%20saver%20after%20going%20around%20the%20houses%20with%20my%20feeble%20attempts.%26nbsp%3B%20Ok%20so%20here%20it%20is%3A-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20blank%20column%20which%20is%20where%20I%20want%20one%20of%20four%20numbers%20to%20appear%20based%20on%20one%20of%20four%20words%20in%20the%20completed%20column.%26nbsp%3B%20How%20can%20I%20use%20a%20formula%20to%20enable%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20is%20where%20I%20have%20text%20already%20completed%2C%20based%20on%20this%20text%20I%20would%20the%20cells%20in%20column%20B%20to%20be%20a%20number%20corresponding%20to%20the%20test.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECOLUMN%20A%26nbsp%3B%20-%20already%20completed%20text%20that%20contain%20one%20of%20the%20words%20below.%3C%2FP%3E%3CP%3EZERO%3C%2FP%3E%3CP%3ELOW%3C%2FP%3E%3CP%3EMEDIUM%3C%2FP%3E%3CP%3EHIGH%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20associate%26nbsp%3B%20number%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EZERO%20%3D%200%3C%2FP%3E%3CP%3ELOW%20%3D%2020%3C%2FP%3E%3CP%3EMEDIUM%20%3D%2050%3C%2FP%3E%3CP%3EHIGH%20%3D%20100%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20how%20to%20I%20populate%20the%20cells%20to%20match%20the%20word%20to%20the%20correct%20number%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMuch%20appreciated%20and%20hope%20this%20makes%20sense!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-562274%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562291%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20got%20a%20little%20lost%20with%20a%20formula%20solution%20and%20now%20confused!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562291%22%20slang%3D%22en-US%22%3EYou%20may%20use%20this%20formula%20in%20Column%20B%3A%3CBR%20%2F%3E%3DLOOKUP(A1%2C%3CBR%20%2F%3E%7B%22High%22%2C%22Low%22%2C%22Medium%22%2C%22Zero%22%7D%2C%3CBR%20%2F%3E%7B100%2C20%2C50%2C0%7D)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562314%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20got%20a%20little%20lost%20with%20a%20formula%20solution%20and%20now%20confused!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341023%22%20target%3D%22_blank%22%3E%40bdaviesprox%3C%2FA%3EHere%20you%20go..%3C%2FP%3E%3CP%3E%3DCONCATENATE(A2%2CIF(A2%3D%22ZERO%22%2C%22%3D%200%22%2CIF(A2%3D%22LOW%22%2C%22%3D%2020%22%2CIF(A2%3D%22MEDIUM%22%2C%22%3D%2050%22%2CIF(A2%3D%22HIGH%22%2C%22%3D%20100%22%2C0)))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562474%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20got%20a%20little%20lost%20with%20a%20formula%20solution%20and%20now%20confused!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562474%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20though%20I%20cant%20seem%20to%20make%20this%20work%20with%20three%20options%20instead%20of%20four%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20with%20another%20data%20set%3A-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLOOKUP(T12%2C%7B%22Open%22%2C%22Mixed%22%2C%22Closed%22%7D%2C%7B20%2C50%2C100%7D)%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3EYou%20may%20use%20this%20formula%20in%20Column%20B%3A%3CBR%20%2F%3E%3DLOOKUP(A1%2C%3CBR%20%2F%3E%7B%22High%22%2C%22Low%22%2C%22Medium%22%2C%22Zero%22%7D%2C%3CBR%20%2F%3E%7B100%2C20%2C50%2C0%7D)%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562475%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20got%20a%20little%20lost%20with%20a%20formula%20solution%20and%20now%20confused!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562475%22%20slang%3D%22en-US%22%3EIf%20I%20do%20this%20then%20my%20cell%20ends%20up%20as%20'High%3D100'%20in%20the%20cell%2C%20I%20just%20want%20the%20figure....%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562515%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20got%20a%20little%20lost%20with%20a%20formula%20solution%20and%20now%20confused!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562515%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341023%22%20target%3D%22_blank%22%3E%40bdaviesprox%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(A2%3D%22ZERO%22%2C0%2CIF(A2%3D%22LOW%22%2C20%2CIF(A2%3D%22MEDIUM%22%2C50%2CIF(A2%3D%22HIGH%22%2C100%2C0))))%3C%2FP%3E%3CP%3ETry%20this%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562547%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20got%20a%20little%20lost%20with%20a%20formula%20solution%20and%20now%20confused!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341023%22%20target%3D%22_blank%22%3E%40bdaviesprox%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELOOKUP%20requires%20the%20names%20to%20be%20in%20ascending%20alphabetical%20order%20so%20you%20need%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLOOKUP(T12%2C%7B%22Closed%22%2C%22Mixed%22%2C%22Open%22%7D%2C%7B100%2C50%2C20%7D)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562604%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20got%20a%20little%20lost%20with%20a%20formula%20solution%20and%20now%20confused!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562604%22%20slang%3D%22en-US%22%3EThe%20lookup_vector%20argument%20of%20LOOKUP%20must%20be%20sorted%20in%20ascending%20order%2C%20as%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%20cited.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562625%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20got%20a%20little%20lost%20with%20a%20formula%20solution%20and%20now%20confused!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562625%22%20slang%3D%22en-US%22%3EIf%20you%20don't%20like%20to%20sort%20the%20lookup_vector%20argument%20in%20ascending%20order%2C%20you%20may%20modify%20the%20formula%20this%20way%3A%3CBR%20%2F%3E%3DLOOKUP(2%2C%3CBR%20%2F%3E1%2F(%7B%22Open%22%2C%22Mixed%22%2C%22Closed%22%7D%3DT12)%2C%3CBR%20%2F%3E%7B20%2C50%2C100%7D)%3C%2FLINGO-BODY%3E
bdaviesprox
New Contributor

Hi there, am sure this will be easy for the community but your help would be a life saver after going around the houses with my feeble attempts.  Ok so here it is:-

 

I have a blank column which is where I want one of four numbers to appear based on one of four words in the completed column.  How can I use a formula to enable this?

 

Column A is where I have text already completed, based on this text I would the cells in column B to be a number corresponding to the test.

 

COLUMN A  - already completed text that contain one of the words below.

ZERO

LOW

MEDIUM

HIGH

 

The associate  number is as follows:

 

ZERO = 0

LOW = 20

MEDIUM = 50

HIGH = 100

 

So how to I populate the cells to match the word to the correct number?

 

Much appreciated and hope this makes sense! 

 

Thank you

 

 

 

8 Replies
You may use this formula in Column B:
=LOOKUP(A1,
{"High","Low","Medium","Zero"},
{100,20,50,0})

@bdaviesproxHere you go..

=CONCATENATE(A2,IF(A2="ZERO","= 0",IF(A2="LOW","= 20",IF(A2="MEDIUM","= 50",IF(A2="HIGH","= 100",0)))))

Thank you, though I cant seem to make this work with three options instead of four? @Twifoo 

 

For example with another data set:-

 

=LOOKUP(T12,{"Open","Mixed","Closed"},{20,50,100})


@Twifoo wrote:
You may use this formula in Column B:
=LOOKUP(A1,
{"High","Low","Medium","Zero"},
{100,20,50,0})

 

If I do this then my cell ends up as 'High=100' in the cell, I just want the figure....

@bdaviesprox 

=IF(A2="ZERO",0,IF(A2="LOW",20,IF(A2="MEDIUM",50,IF(A2="HIGH",100,0))))

Try this

Solution

@bdaviesprox 

LOOKUP requires the names to be in ascending alphabetical order so you need

=LOOKUP(T12,{"Closed","Mixed","Open"},{100,50,20})

The lookup_vector argument of LOOKUP must be sorted in ascending order, as @Peter Bartholomew cited.
If you don't like to sort the lookup_vector argument in ascending order, you may modify the formula this way:
=LOOKUP(2,
1/({"Open","Mixed","Closed"}=T12),
{20,50,100})