Changing characters into numbers

[rothmaniac]

I am trying to do a Vlookup based on 2 variables, instead of one.
I came up with a novel way of doing it, which is to create an ID numbe
unique to those 2 variables, and doing a Vlookup based on these I
numbers.

What I would like to do is change every letter in a text string to
numeric value, and multiply all of these numbers. Then I would like t
multiply that number with another number created the same way
hopefully creating a very large, unique ID number.

For example, lets say i have the following 2 varialbes:
ABC
DOG

I would like to have a function that converts 'ABC' to
1*2*3
And DOG to
4*15*7
Multiple these numbers together, giving me my unique ID number of 2520

The numbers I just used are for example, it doesn't matter what th
letters are converted to, just as long as it's constant.
The variables I am using are all larger text strings, and will give m
large, unique numbers.
I would need to do this in 1 step preferably, because I will need t
input this function into a VLOOKUP function



I have a feeling I can do this with the CODE function, but it will onl
convert and return the first letter.


Any creative suggestions

 

[Frank Kabel]

Hi
try the array form,ula (entered with CTRL+SHIFT+ENTER):
=PRODUCT(CODE(MID(A1,ROW(INDIRECT("1:" & LEN(A1)));1))-64)

 

[Aladin Akyurek]

Is it not much easier to create an additional column where you concatenate
those 2 variables...

Let B210 house the lookup table of interest...

In A2 enter & copy down:

=B2&CHAR(127)&C2

Let F2 and G2 house the lookup values of interest. The VLOOKUP formula that
you need becomes:

=VLOOKUP(F2&CHAR(127)&G2,$A$2:$D$10,4,0)