CASE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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))
 
Could you possibly explain how this logic works. I looked up VLOOKUP on MS
help and it's not very clear

Thanks again
 
GregNga said:
Could you possibly explain how this logic works...
Click to expand...

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)

---
 
Back
Top