Hello to you guys. I want to know if u guys can help me solve it.. this
isnt going to be easy guys.. but if u guys know a formula.. please help
me

A B C D
Brand Product Name MotorCycle Name Price
FMS Head Lamp Supra $15
FMS Head Lamp Tornado $25
FMS Exhaust Pipe Supra $80
FMS Exhaust Pipe Tornado $120
KGW Head Lamp Supra $40
KGW Head Lamp Tornado $50
KGW Exhaust Pipe Supra $150
KGW Exhaust Pipe Tornado $180

Here are some of my examples from my Price list. Im trying to use a
simple invoice with vlookup to bill my customer... lets say my customer
order

Quantity Brand Product Name MotorCycle Name Price
10 KGW Head Lamp Supra ?

The price if we look at it manually its $40 But how to use a formula
which it auto calculate the price for me? its not easy... i think we
need to use vlookup and if formula? Please help me!!


--
Hero_honda
------------------------------------------------------------------------
Hero_honda's Profile: http://www.excelforum.com/member.php...o&userid=34713
View this thread: http://www.excelforum.com/showthread...hreadid=544797

Hi,
Maybe you can use sumproduct()

If your above data starts with Brand in A1 then some thing like this:

=SUMPRODUCT(--(A2:A9=A11),--(B2:B9=B11),--(C2:C9=C11),D2:D9)*D11

Where the brand is in A11, the Product Name in B11 and the MC Name in
C11 and quantity in D11

Reagards,
Bondi

Assuming source table as posted is in sheet: X
within A1:D100 (say), data from row2 down
Col A = brand, B = Prouct Name, C = Motorcycle Name, D = Unit Price

In your invoice sheet,
this table below is in cols A to D,
with "Price" to be computed in col E (= Qty x Unit Price from X)

Quantity Brand Product Name MotorCycle Name Price
10 KGW Head Lamp Supra ?
.....
(data from row2 down)

Put in the formula bar for E2, then array-enter the formula
by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX(X!$D$2:$D$10,MATCH(1,(X!$A$2:$A$10=B2)*(X!$ B$2:$B$10=C2)*(X!$C$2:$C$10=D2),0))*A2

Adapt the ranges to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Hero_honda" wrote:

Hello to you guys. I want to know if u guys can help me solve it.. this
isnt going to be easy guys.. but if u guys know a formula.. please help
me

A B C D
Brand Product Name MotorCycle Name Price
FMS Head Lamp Supra $15
FMS Head Lamp Tornado $25
FMS Exhaust Pipe Supra $80
FMS Exhaust Pipe Tornado $120
KGW Head Lamp Supra $40
KGW Head Lamp Tornado $50
KGW Exhaust Pipe Supra $150
KGW Exhaust Pipe Tornado $180

Here are some of my examples from my Price list. Im trying to use a
simple invoice with vlookup to bill my customer... lets say my customer
order

Quantity Brand Product Name MotorCycle Name Price
10 KGW Head Lamp Supra ?

The price if we look at it manually its $40 But how to use a formula
which it auto calculate the price for me? its not easy... i think we
need to use vlookup and if formula? Please help me!!


--
Hero_honda
------------------------------------------------------------------------
Hero_honda's Profile: http://www.excelforum.com/member.php...o&userid=34713
View this thread: http://www.excelforum.com/showthread...hreadid=544797

Typo, line:
within A1:D100 (say),

should read as:
within A1:D10 (say),
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad that you got it all sorted out ok!! :)

Max - out of interest could you tell me why there is a 1 after the
MATCH statement? I understand how the rest of it works - and will be
able to put that to good use in my own spreadsheets - just can't follow
that bit!

Cheers

Carl

Put in the formula bar for E2, then array-enter the formula
by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX(X!$D$2:$D$10,MATCH(1,(X!$A$2:$A$10=B2)*(X!$ B$2:$B$10=C2)*(X!$C$2:$C$10=D2),0))*A2


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=544797

"mr_teacher" wrote:
Max - out of interest could you tell me why there is a 1 after the
MATCH statement?

The <array within MATCH:
(X!$A$2:$A$10=B2)*(X!$B$2:$B$10=C2)*(X!$C$2:$C$Â*1 0=D2)
would return an array of zeros "0" with a single* "1"
eg: {0;1;0;0;0;0;0;0;0}
*uniques implicitly assumed

where the single "1" within the array
would be the row within cols A, B and C in X
satisfying all 3 conditions of being equal to A2, B2 and C2

MATCH(1,<array,0) then returns the position
within the array that matches the "1" (i.e.: 2)
to retrieve the corresp value from INDEX(X!$D$2:$D$10,..

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Typos in line:
satisfying all 3 conditions of being equal to A2, B2 and C2

should read as:
satisfying all 3 conditions of being equal to B2, C2 and D2

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I have:
1. the brands (for example: FMS, KGW, KGT)
2. Motorcycle names (for example: CB100, GL100, A100, Supra... etc)
3. Motorcycle parts (For example: Head lamp, exhausht pipe, regulator,
etc etc)

with so many motorcycle names and so many parts how do i limit the drop
down list when i select the parts name? instead of using drop down list
for all my motorcycle names....

for exmple:
Motor names Parts brand
CB100 Head Lamp FMS
GL100 Head Lamp FMS
Supra Head Lamp FMS
Prima Head Lamp FMS
Cb100 Exhaust Pipe FMS
C70 exhaust pipe FMS
Tornado exhaust pipe FMS

looking at above, i want to use drop down list.. if i select exhaust
pipe from the drop down menu, i want excel to auto show me there are
cb100, c70 and tornado on exhaust pipe.. instead of showing me all the
motorcycle names..


--
Hero_honda
------------------------------------------------------------------------
Hero_honda's Profile: http://www.excelforum.com/member.php...o&userid=34713
View this thread: http://www.excelforum.com/showthread...hreadid=544797

See if this gets you pointed in the right direction:

With your data in cells A1:D9, including column titles.

F1: 10
G1: KGW
H1: Head Lamp
I1: Supra
J1: =INDEX($D$1:$D$9,MATCH(G1&H1&I1,$A$1:$A$9&$B$1:$B$ 9&$C$1:$C$9,0))

The ARRAY FORMULA* formula in J1 concatenates the lookup parameters and
searches for them in the concatenated lookup table columns.

An alternative, approach would be to insert a column in front of the
table (in Col_A, moving the table to the right) and concatenate the
fields there.

Example:
A1: B1&C1&D1

Then the formulas could be this:
F1: 10
G1: KGW
H1: Head Lamp
I1: Supra
J1: =VLOOKUP(G1&H1&I1,$A$1:$E$9,5,0))

Note: If there is no match, the Col_J formulas would return an error.
To avoid that, the new formulas would be:

J1 (ARRAY FORMULA*):
=IF(ISNA(INDEX($D$1:$D$9,MATCH(G1&H1&I1,$A$1:$A$9& $B$1:$B$9&$C$1:$C$9,0))),"",INDEX($D$1:$D$9,MATCH( G1&H1&I1,$A$1:$A$9&$B$1:$B$9&$C$1:$C$9,0)))

or
If using thelookup values in Col_A:
J1:
=IF(ISNA(VLOOKUP(H1&I1&J1,$A$1:$E$9,5,0)),"",VLOOK UP(H1&I1&J1,$A$1:$E$9,5,0))

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that give you something to work with?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=544797

Hero_honda

Variations of the SUMPRODUCT approach....

If you use Col_A for the lookup values (eg b2&c2&d2):
J1: =SUMPRODUCT(($A$2:$A$9=G1&H1&I1)*$E$2:$E$9)

or...if you use your table as posted:
J1:
=SUMPRODUCT(($A$2:$A$9=G1)*($B$2:$B$9=H1)*($C$2:$C $9=I1)*$E$2:$E$9)

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=544797

Ok, sure there is an easier way than this - but this is what I would
try.

Firstly I would insert a new row at the start, so column A becomes
blank. Then in cell A2 I would put the formula

=CONCATENATE(B2,C2,D2)

I am guessing that you want to be able to select a variety of brands,
products etc.

In Cell F1 I would put the header Brand
In Cell G1 I would put the header Product Name
In Cell H1 I would put the header MotorCycle Name
In Cell I1 I would put the header Quantity

This will allow you to type your choice into each area in row 2

I would consider creating some drop down lists for your entries though
to avoid any typing errors which would mess the whole thing up.

Then in cell J2 you could put the formula

=VLOOKUP(CONCATENATE(F2,G2,H2),A:E,5,FALSE)*I2

As I say i am sure this is not the easiest way to do this - and
hopefully someone here will show an easier way that I could use myself
too - but as I am often told on here it is best to keep on learning!!
:)

Hope his is some help - or at least gives you some ideas!!

Regards

Carl

Hero_honda Wrote:
Hello to you guys. I want to know if u guys can help me solve it.. this
isnt going to be easy guys.. but if u guys know a formula.. please help
me

A B C D
Brand Product Name MotorCycle Name Price
FMS Head Lamp Supra $15
FMS Head Lamp Tornado $25
FMS Exhaust Pipe Supra $80
FMS Exhaust Pipe Tornado $120
KGW Head Lamp Supra $40
KGW Head Lamp Tornado $50
KGW Exhaust Pipe Supra $150
KGW Exhaust Pipe Tornado $180

Here are some of my examples from my Price list. Im trying to use a
simple invoice with vlookup to bill my customer... lets say my customer
order

Quantity Brand Product Name MotorCycle Name Price
10 KGW Head Lamp Supra ?

The price if we look at it manually its $40 But how to use a formula
which it auto calculate the price for me? its not easy... i think we
need to use vlookup and if formula? Please help me!!


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=544797

Thanks for many replies guys... i appreciate your help...

I find mr teacher's way is the best solution for me.. easy and quick
formula.. many thanks for the quick replies..

this problem been with me for so long till out of somewhere i found
this forum and i thought why not ask experts to solve my problems..
indeed it worked.. many thanks to you guys.. did a good job for me..
thank you


--
Hero_honda
------------------------------------------------------------------------
Hero_honda's Profile: http://www.excelforum.com/member.php...o&userid=34713
View this thread: http://www.excelforum.com/showthread...hreadid=544797