Asigning a value to words

T

Tim

I have a cell ie. A3 which is a word and I want to tell cell A5 that whenever
it sees this word to asign it a value of 5 foe example.

Orange=3

Is this possible
 
P

Pete_UK

You could build up a table, eg M1:N5 as follows:

Apple 2
Banana 4
Grape 7
Orange 5
Pear 3

Then in A5 enter:

=IF(A3="","",VLOOKUP(A3,M1:N5,2,0))

Hope this helps.

Pete
 
T

Tim

Thanks that seems to work but can I tell it to go through the whole sheet and
wherever it sees orange put 3
 
T

Tim

Thankyou, Once I have built the table how do I make it go through the whole
work sheet. Would I have to copy the formula in all the cells?
 
D

Dave Peterson

How about using a bunch of Edit|Replace's?
Thanks that seems to work but can I tell it to go through the whole sheet and
wherever it sees orange put 3
Click to expand...
 
T

Tim

Pete, When I place the formula and try to copy into the cell belowA4 the
values of M1:N5 change within the formula so the more I put in the table more
gets missed out as I copy the formula into the sheet, any ideas.
 
P

Pete_UK

I didn't realise you would be copying it down as you said the word was
in A3 and you wanted the return value to be in A5 (only room for one
more word). Normally you would arrange the words in one column (A) and
put the formula in another column (B), so that you could then copy
down as many as you wish. Change the formula to this:

=IF(A3="","",VLOOKUP(A3,M$1:N$5,2,0))

so now when you copy it down you will always be looking at the same
table (rather than M2:N6, M3:N7 etc as it was before. If you have more
than 5 reference words, so that the table occupies say M1:N35, then
change the formula to:

=IF(A3="","",VLOOKUP(A3,M$1:N$35,2,0))

then copy down.

Hope this helps.

Pete
 
T

Tim

Cheers worked a treat,
--
Tim


Pete_UK said:
I didn't realise you would be copying it down as you said the word was
in A3 and you wanted the return value to be in A5 (only room for one
more word). Normally you would arrange the words in one column (A) and
put the formula in another column (B), so that you could then copy
down as many as you wish. Change the formula to this:

=IF(A3="","",VLOOKUP(A3,M$1:N$5,2,0))

so now when you copy it down you will always be looking at the same
table (rather than M2:N6, M3:N7 etc as it was before. If you have more
than 5 reference words, so that the table occupies say M1:N35, then
change the formula to:

=IF(A3="","",VLOOKUP(A3,M$1:N$35,2,0))

then copy down.

Hope this helps.

Pete
Click to expand...
 
P

Pete_UK

The formula will take the word in A3 and convert it to the appropriate
number as specified in the table (and return this value to the cell
where the formula is located). If you have other words scattered about
the sheet then you would need a similar formula in another cell, but
you would need to change the two references to A3 to suit your other
cells. If you copy the formula, then you would need to paste it into a
cell which has the same positional relationship as you have at the
moment (i.e. if this formula is in A5 and you had a word in B6, you
would need to copy the formula into B8). If you want to copy into
different columns, however, you will need to change the formula to:

=IF(A3="","",VLOOKUP(A3,$M$1:$N$5,2,0))
or
=IF(A3="","",VLOOKUP(A3,$M$1:$N$35,2,0))

so that you are always referencing the same table.

Hope this helps.

Pete

Hope this helps.

Pete
 
Top