Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Corey Osborn
 
Posts: n/a
Default 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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
Biff
 
Posts: n/a
Default Vlookup and left function

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 wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Vlookup and left function

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";"H XA2";"HXH2";"H9MPV"}&"*",B2:B100))*40



--

Regards,

Peo Sjoblom




"Corey Osborn" <Corey wrote in message
...
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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
reno
 
Posts: n/a
Default Vlookup and left function

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup returns column to the left Brisbane Rob Excel Worksheet Functions 3 October 9th 05 12:33 AM
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION CHAIM Excel Worksheet Functions 1 July 27th 05 09:10 PM
Can Vlookup check a cell to the left? koala Excel Worksheet Functions 3 July 13th 05 02:31 AM
Vlookup and Hlookup Phlogiston2312 Excel Worksheet Functions 1 April 21st 05 04:59 PM
VLookup ... Left side? Ken Excel Worksheet Functions 1 January 25th 05 01:25 PM


All times are GMT +1. The time now is 12:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"