Vlookup and left function

C

Corey Osborn

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.
 
B

Biff

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
 
P

Peo Sjoblom

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
 
R

reno

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".
 
Top