How can 0 be used in the Choose function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Max - 16 Jun 2007 11:06 GMT

Hello,
We download data from a software package that uses numbers 0,1,2,3,4 to
indicate a catagory.
0 - Salary
1- Medical Aid
2 - Benefits
3 - Car Allowance
4 - Mobile Allowance

This information is downloaded into Excel into a table.
Want to convert the the 0, 1, 2, etc into the relevant string (Salary or
Medical Aid etc), the function Choose does not allow 0 to be as an index.
Is there a alternative formula, or is there a way to get the Choose function
to read the 0?

Thank you and best regards

Max

Reply to this Message

Bob Phillips - 16 Jun 2007 11:11 GMT

=CHOOSE(A2+1,"Salary","Medical Aid","Benefits","Car Allowance","Mobile
Allowance")

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello,
> We download data from a software package that uses numbers 0,1,2,3,4 to
[quoted text clipped - 15 lines]
>
> Max

Reply to this Message

Max - 16 Jun 2007 11:23 GMT

Well done and thank you Bob.
It was so simple.

> =CHOOSE(A2+1,"Salary","Medical Aid","Benefits","Car Allowance","Mobile
> Allowance")
[quoted text clipped - 18 lines]
> >
> > Max

Reply to this Message

ShaneDevenshire - 16 Jun 2007 21:41 GMT

Hi,

Here are some other alternatives:
If you set up a table in Excel which looks like:

0    Salary
1    Medical Aid
2    Benefits
3    Car Allowance
4    Mobile Allowance

say in the range G1:H5 then you could use any of these formulas among many
others:

=VLOOKUP(A1,G1:H5,2)
=VLOOKUP(A1,{0,"Salary";1,"Medical Aid";2,"Benefits";3,"Car
Allowance";4,"Mobile Allowance"},2)
=LOOKUP(A1,G1:G5,H1:H5)
=LOOKUP(A1,G1:G5,{"Salary";"Medical Aid";"Benefits";"Car Allowance";"Mobile
Allowance"})
or shortest of all
=LOOKUP(A1,G1:H5)

Signature

Cheers,
Shane Devenshire

> Hello,
> We download data from a software package that uses numbers 0,1,2,3,4 to
[quoted text clipped - 14 lines]
>
> Max

Reply to this Message