I'm trying to link these three words to their alternative number in excel,

not started, in progress, and done to 0, 0.5, and 1 respectively.

I tried to create a list of states and used them in column A, the same as the following image.

then I wrote a simple code in column B respectivly to achieve my desire like this:

=LOOKUP(A1,{"not started","in Progress","done"},{0,0.5,1})

(A1 changed to A2 and A3, respectively)

But the result was weird, and I do not have any idea what is my misunderstanding about the function lookup. here is the result: any help would be appreciated.

CodePudding user response：

See LOOKUP Vector form:

The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value.

So, it should be: LOOKUP(A1,{"done","in progress","not started"},{1,0.5,0}) with 'd', 'i', 'n'.

If you don't want to bother with figuring out the correct order, you could use INDEX/MATCH:

=INDEX({0,0.5,1},MATCH(A1,{"not started","in progress","done"},0))

CodePudding user response：

Why not IFS.

=IFS(A1="not started",0,A1="in progress",0.5,A1="done",1)