looking to assign different numbers in column B to different words in column A
 

Hi,

I am having a fairly simple issue but can't figure it out. Please see
example.


A B
food
food
food
food
drink
drink
drink
drink
fruit
fruit
fruit
fruit

In B column I am looking for a formula which I can drag down, that
will assign a 5 for food a 10 for drink and 15 for fruit so at the end
it will look something like this:

A B
food 5
food 5
food 5
drink 10
drink 10
drink 10
fruit 15
fruit 15
fruit 15

I pretty much want to tell it that if A is fruit then B should be 5,
if A is drink then B should be 10 and if A is fruit then B should be
15. I would like to have all that in the same formula. Now obviously
my situation is more complicated than this so other advice than what
Im asking for may not work for me.

Please help

Mike

With one formula you could do it like this;

=CHOOSE(MATCH(A1,{"food","drink","fruit"},0),5,10, 15)

If you have a giant list of values to match with texts, it would be easier
to create that list and corresponding values in a separate table, then use a
VLOOKUP or INDEX/MATCH formula to find the matching value.

e.g.

=VLOOKUP("food",F1:G20,2,FALSE)

with F1:F20 containing labels(words) and G1:G20 containing values.




"mike" wrote in message
om...
Hi,

I am having a fairly simple issue but can't figure it out. Please see
example.


A B
food
food
food
food
drink
drink
drink
drink
fruit
fruit
fruit
fruit

In B column I am looking for a formula which I can drag down, that
will assign a 5 for food a 10 for drink and 15 for fruit so at the end
it will look something like this:

A B
food 5
food 5
food 5
drink 10
drink 10
drink 10
fruit 15
fruit 15
fruit 15

I pretty much want to tell it that if A is fruit then B should be 5,
if A is drink then B should be 10 and if A is fruit then B should be
15. I would like to have all that in the same formula. Now obviously
my situation is more complicated than this so other advice than what
Im asking for may not work for me.

Please help

Mike

marin

If those are the only choices.......

In B1 enter =IF(A1="food",5,IF(A1="drink",10,15))

Copy down Column B

If just a representative sample of many more items, I would suggest a VLOOKUP
table formula.

For more on that see Debra Dalgleish's site.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben Excel MVP

On 12 Jan 2005 14:39:35 -0800, (mike) wrote:

Hi,

I am having a fairly simple issue but can't figure it out. Please see
example.


A B
food
food
food
food
drink
drink
drink
drink
fruit
fruit
fruit
fruit

In B column I am looking for a formula which I can drag down, that
will assign a 5 for food a 10 for drink and 15 for fruit so at the end
it will look something like this:

A B
food 5
food 5
food 5
drink 10
drink 10
drink 10
fruit 15
fruit 15
fruit 15

I pretty much want to tell it that if A is fruit then B should be 5,
if A is drink then B should be 10 and if A is fruit then B should be
15. I would like to have all that in the same formula. Now obviously
my situation is more complicated than this so other advice than what
Im asking for may not work for me.

Please help

Mike