SOLVED

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

Copper 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

best response confirmed by bdaviesprox (Copper Contributor)
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})
1 best response

Accepted Solutions
best response confirmed by bdaviesprox (Copper Contributor)
Solution

@bdaviesprox 

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

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

View solution in original post