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

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

Post by marin_mich » Fri, 14 Jan 2005 07:39:35


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
 
 
 

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

Post by Dave R » Fri, 14 Jan 2005 08:03:27

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.

 
 
 

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

Post by Gord Dibbe » Fri, 14 Jan 2005 10:46:40

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.yqcomputer.com/


Gord Dibben Excel MVP