if function

  • Thread starter Thread starter hitesh
  • Start date Start date
H

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
 
hitesh said:
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.
Click to expand...

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.
 
Enter in B1

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


Gord Dibben MS Excel MVP
 
JoeU2004 said:
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.
Click to expand...

thanks a lot.
 
Back
Top