SOLVED

Highlighted
New Contributor

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

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
Highlighted

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

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

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

@bdaviesproxHere you go..

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

Highlighted

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

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})

Highlighted

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

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

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

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

Try this

Highlighted
Solution

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

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

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

Highlighted

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

The lookup_vector argument of LOOKUP must be sorted in ascending order, as @Peter Bartholomew cited.
Highlighted

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

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})