Forum Discussion
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
LOOKUP requires the names to be in ascending alphabetical order so you need
=LOOKUP(T12,{"Closed","Mixed","Open"},{100,50,20})
8 Replies
bdaviesproxHere you go..
=CONCATENATE(A2,IF(A2="ZERO","= 0",IF(A2="LOW","= 20",IF(A2="MEDIUM","= 50",IF(A2="HIGH","= 100",0)))))
- If I do this then my cell ends up as 'High=100' in the cell, I just want the figure....
- You may use this formula in Column B:
=LOOKUP(A1,
{"High","Low","Medium","Zero"},
{100,20,50,0})- 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})
