Forum Discussion

Copper Contributor

May 12, 2019

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

excel

Formulas and Functions

PeterBartholomew1
May 13, 2019
bdaviesprox
LOOKUP requires the names to be in ascending alphabetical order so you need
=LOOKUP(T12,{"Closed","Mixed","Open"},{100,50,20})

Twifoo

Silver Contributor

May 12, 2019

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

bdaviesprox
Copper Contributor
May 13, 2019
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})
- Twifoo
  Silver Contributor
  May 13, 2019
  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})
- Twifoo
  Silver Contributor
  May 13, 2019
  The lookup_vector argument of LOOKUP must be sorted in ascending order, as PeterBartholomew1 cited.
- PeterBartholomew1
  Silver Contributor
  May 13, 2019
  bdaviesprox
  LOOKUP requires the names to be in ascending alphabetical order so you need
  =LOOKUP(T12,{"Closed","Mixed","Open"},{100,50,20})