 • 412K Members
• 4,045 Online
• 468K Conversations
SOLVED

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

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

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

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

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....

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

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

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.

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})
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies