if function

[hitesh]

dear

if i type 511 in cell A1 then in cell B1 there will be anil,if i type 512 in
cell A1 then in cell B1 there will be sunil. if i type 513 in cell A1 then in
cell B1 there will be amit. if i type 514 in cell A1 then in cell B1 there
will be ritesh.
is there any formula for this.

pls help

 

[JoeU2004]

if i type 511 in cell A1 then in cell B1 there will be anil,if i type 512
in
cell A1 then in cell B1 there will be sunil. if i type 513 in cell A1 then
in
cell B1 there will be amit. if i type 514 in cell A1 then in cell B1 there
will be ritesh. is there any formula for this.

There are many ways to do this, depending on how robust you want to be about
error conditions, i.e. when A1 is none of those values.

Ignoring error conditions, you could write simply:

=choose(A1-510,"anil","sunil","amit","ritesh")

A little more bullet-proof:

=if(and(511<=A1,A1<=514),choose(A1-510,"anil","sunil","amit","ritesh"),"")

If you might have more than 29 names (the limit for CHOOSE), you might want
to set up a table in a range and use VLOOKUP.

 

[Gord Dibben]

Enter in B1

=LOOKUP(A1,{511,512,513,514},{"anil","sunil","amit","ritesh"})


Gord Dibben MS Excel MVP

 

[hitesh]

There are many ways to do this, depending on how robust you want to be about
error conditions, i.e. when A1 is none of those values.

Ignoring error conditions, you could write simply:

=choose(A1-510,"anil","sunil","amit","ritesh")

A little more bullet-proof:

=if(and(511<=A1,A1<=514),choose(A1-510,"anil","sunil","amit","ritesh"),"")

If you might have more than 29 names (the limit for CHOOSE), you might want
to set up a table in a range and use VLOOKUP.

thanks a lot.

 

[hitesh]

Enter in B1

=LOOKUP(A1,{511,512,513,514},{"anil","sunil","amit","ritesh"})


Gord Dibben MS Excel MVP

thanks a lot