I want to do something similar to CASE in SQL. That is if cell A1 is 'A' then
I went 'ACTIVE' in B1. if cell A1 is 'D' then I went 'DELETED' in B1. if cell
A1 is 'C' then I went 'CLOSED' in B1.

How to do this?

One way ..

Put in B1:
=IF(A1="","",VLOOKUP(A1,{"A","ACTIVE";"D","DELETED ";"C","CLOSED"},2,0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"GregNga" wrote:
I want to do something similar to CASE in SQL. That is if cell A1 is 'A' then
I went 'ACTIVE' in B1. if cell A1 is 'D' then I went 'DELETED' in B1. if cell
A1 is 'C' then I went 'CLOSED' in B1.

How to do this?

Could you possibly explain how this logic works. I looked up VLOOKUP on MS
help and it's not very clear

Thanks again

"Max" wrote:

One way ..

Put in B1:
=IF(A1="","",VLOOKUP(A1,{"A","ACTIVE";"D","DELETED ";"C","CLOSED"},2,0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"GregNga" wrote:
I want to do something similar to CASE in SQL. That is if cell A1 is 'A' then
I went 'ACTIVE' in B1. if cell A1 is 'D' then I went 'DELETED' in B1. if cell
A1 is 'C' then I went 'CLOSED' in B1.

How to do this?

"GregNga" wrote:
Could you possibly explain how this logic works...

VLOOKUP(A1,{"A","ACTIVE";"D","DELETED";"C","CLOSED "},2,0)

The fixed table_array used in the vlookup, ie:
{"A","ACTIVE";"D","DELETED";"C","CLOSED"}

is the same as a 2 col x 3 row range on the sheet:

A ACTIVE
D DELETED
C CLOSED

The lookup value in A1 is compared with the values within the 1st col above,
and where it matches exactly*, the corresponding value in the 2nd** col will
then be returned by the vlookup

*exact matching is specified by the last zero (or FALSE) in the
vlookup(..,2,0)
**that's the col index num: 2 in the vlookup(..,2,0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---