Hello all,

I am working with the following cells

A B

HAC024GKC 2
HAC418GKC 4
HAC224GKC 16

I would like to write a funcition that looks at the first 4 integers of the
cells in Column a and performs an output based on those integers.

Example : If it starts with HAC0 then multiply 10 times the field adjacent
to the right

If it starts with HAC4 then multiply 30 time the adjacent field.

If it starts with HAC2 then multiply 20 times the adjacent field.

My thought were to use the "LEFT" function inside of a "LOOKUP" Function.
The reason I am using the lookup instead of an "IF" function is because I
will be looking at more than 7 values. I am getting all sorts of errors when
I try this.

Any ideas?

Hi ASHA27,

You can use left within a LOOKUP or VLOOKUP formula but you either need
a separate table to show HAC0, HAC4 etc and the corresponding values or
you'll have to include all those in the formula, the first option (a
table) is normally the best, e.g. if you have your "HAC0" etc in C1
down and the corresponding multiplier in D1 down

=VLOOKUP(LEFT(A2),C:D,2,0)*B2

If all your text in column A begins with "HAC" perhaps you could simply
look at the 4th character...

=VLOOKUP(MID(A2,4,1)+0,C:D,2,0)*B2


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=516565

Try this in C1:

=LOOKUP(--MID(A1,4,1),{0,2,4;10,20,30})*B1

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"ASHA27" wrote in message
...
Hello all,

I am working with the following cells

A B

HAC024GKC 2
HAC418GKC 4
HAC224GKC 16

I would like to write a funcition that looks at the first 4 integers of the
cells in Column a and performs an output based on those integers.

Example : If it starts with HAC0 then multiply 10 times the field adjacent
to the right

If it starts with HAC4 then multiply 30 time the adjacent field.

If it starts with HAC2 then multiply 20 times the adjacent field.

My thought were to use the "LEFT" function inside of a "LOOKUP" Function.
The reason I am using the lookup instead of an "IF" function is because I
will be looking at more than 7 values. I am getting all sorts of errors
when
I try this.

Any ideas?