Vlookup and left function

I have 2 columns:

Model qty
HAC4##### 1
HHP4##### 2
HXA4##### 1
HXA2##### 3
HXH2##### 3
H9MPV#### 4
chair 1
toys 1
benchmark 1

HAC4, HHP4, HXA4, HXA2, HXH2, & H9MPV are the common identifiers that I am
looking for. I would like to write an equations that looks for these common
identifiers and muliplies 40 x the QTY column.

Hi!

So, you want the total of ALL entries that match the the variables?

Make a list of those variables:

F1:F6 =

HAC4
HHP4
HXA4
HXA2
HXH2
H9MPV


Then:

=SUMPRODUCT(--(ISNUMBER(SEARCH(F1:F6,A1:A9))),B1:B9)*40

Biff

"Corey Osborn" <Corey Osborn@discussions.microsoft.com> wrote in message
news:11C13388-32BB-4A0A-B07B-40E2D2728E62@microsoft.com...
>I have 2 columns:
>
> Model qty
> HAC4##### 1
> HHP4##### 2
> HXA4##### 1
> HXA2##### 3
> HXH2##### 3
> H9MPV#### 4
> chair 1
> toys 1
> benchmark 1
>
> HAC4, HHP4, HXA4, HXA2, HXH2, & H9MPV are the common identifiers that I am
> looking for. I would like to write an equations that looks for these
> common
> identifiers and muliplies 40 x the QTY column.
>

Do you mean to look them up one by one?

=VLOOKUP("HXA2*",A2:B10,2,0)*40

would return 120 using your example

or

=VLOOKUP(C1&"*",A2:B10,2,0)*40

where you would put the criterion in C1

or do you mean that you want to total the whole range, sum the respective
numbers and multiply the total with 40? Using your example would be 14*40 =
560 If the latter use

=SUMPRODUCT(SUMIF(A2:A100,{"HAC4";"HHP4";"HXA4";"HXA2";"HXH2";"H9MPV"}&"*",B2:B100))*40



--

Regards,

Peo Sjoblom




"Corey Osborn" <Corey Osborn@discussions.microsoft.com> wrote in message
news:11C13388-32BB-4A0A-B07B-40E2D2728E62@microsoft.com...
>I have 2 columns:
>
> Model qty
> HAC4##### 1
> HHP4##### 2
> HXA4##### 1
> HXA2##### 3
> HXH2##### 3
> H9MPV#### 4
> chair 1
> toys 1
> benchmark 1
>
> HAC4, HHP4, HXA4, HXA2, HXH2, & H9MPV are the common identifiers that I am
> looking for. I would like to write an equations that looks for these
> common
> identifiers and muliplies 40 x the QTY column.
>

i assume the HAC4##### indicates there are multiple items of HAC4, the
easiest way would be to sort then just multiply them as they "sit".
"Corey Osborn" wrote:

> I have 2 columns:
>
> Model qty
> HAC4##### 1
> HHP4##### 2
> HXA4##### 1
> HXA2##### 3
> HXH2##### 3
> H9MPV#### 4
> chair 1
> toys 1
> benchmark 1
>
> HAC4, HHP4, HXA4, HXA2, HXH2, & H9MPV are the common identifiers that I am
> looking for. I would like to write an equations that looks for these common
> identifiers and muliplies 40 x the QTY column.
>