choose formula help

[ganeshpoojary05]

Hi all,

I need a help in a formula. I'm pasting the example below:

Range(A1) can contain "A", "B", "C", "D", "E", or "F"

and if A1 contains A then it should return a value 100, if B then 200, if C then 300, if D then 400, if E then 500, if F then 600. I know this can be possible by If condition, but i need some more easier and short formula. Please help. Thanks in advance for your assistance.

 

[SteveO59L]

Try tabulating your relationships and using vlookup()

 

[Marcelo Branco]

Hi,

If you are saying that A1 can be equal to A, B....maybe this

=LOOKUP(A1,{"A";"B";"C";"D";"E";"F"},{100;200;300;400;500;600})

HTH

M.

 

[West Man]

Or shorter alternative: =100*(CODE(A1)-64)

 

[T. Valko]

Hi all,

I need a help in a formula. I'm pasting the example below:

Range(A1) can contain "A", "B", "C", "D", "E", or "F"

and if A1 contains A then it should return a value 100, if B then 200, if C then 300, if D then 400, if E then 500, if F then 600. I know this can be possible by If condition, but i need some more easier and short formula. Please help. Thanks in advance for your assistance.

Assuming the entries can only be the letters A thru F...

=IF(A1="","",SEARCH(A1,"ABCDEF")*100)

Or:

=SEARCH(A1,"ABCDEF")*100*(A1<>"")

 

[ganeshpoojary05]

Hi Valko.

Thanks for your formula, but if the options are not in the multiples of 100, then what would be the formula.

 

[T. Valko]

Hi Valko.

Thanks for your formula, but if the options are not in the multiples of 100, then what would be the formula.

Then you can use a formula like Marcelo Branco posted.

 

[ganeshpoojary05]

Hi Valko.

Thanks for your formula, but if the options are not in the multiples of 100, then what would be the formula.

But in his formula, the exact value is not returned for example if the cell A1 is other than ABCDEF then to the value is returned.

 

[T. Valko]

But in his formula, the exact value is not returned for example if the cell A1 is other than ABCDEF then to the value is returned.

OK, if the cell contains something other than ABCDEF what result do you want?

What version of Excel are you using?

 

[taurean]

You will need to either combine the solutions to suit your requirements like:
=IF((CODE(A1)-64)<=6,LOOKUP(A1,{"A";"B";"C";"D";"E";"F"},{100;200;300;400;500;600}),"NOT FOUND")
Or use a lookup table as suggested at #2