Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
Typo, line:
within A1:D100 (say), should read as: within A1:D10 (say), -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
"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 --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 --- |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
Hi
Take a look at Data Validation at Debra Dalgleish's site, especially dependent dropdowns http://www.contextures.com/xlDataVal13.html -- Regards Roger Govier "Hero_honda" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
Im going to use drop down list on my invoice.. do u think it will work roger? coz in my invoice, it going to have Quantity Motorcycle-name Motorcycle-parts Brand Price im going to use Drop down list on Motorcycle name, motorcycle parts and brand... -- 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
Hi
Absolutely no reason why it wouldn't work. This technique is often used for producing invoices. -- Regards Roger Govier "Hero_honda" wrote in message ... Im going to use drop down list on my invoice.. do u think it will work roger? coz in my invoice, it going to have Quantity Motorcycle-name Motorcycle-parts Brand Price im going to use Drop down list on Motorcycle name, motorcycle parts and brand... -- 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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
A100 AS OVERAN FMS 11,500 V80 AS OVERAN FMS 11,500 WIN AS OVERAN FMS 11,500 YB100 AS OVERAN FMS 11,500 A100 AS SHOCK DEPAN FMS 65,000 CB100K3 AS SHOCK DEPAN FMS 65,000 FORCE-1 AS SHOCK DEPAN FMS 50,000 GL-PRO AS SHOCK DEPAN FMS 67,500 SMASH AS OVERAN KGW 50,000 V80 AS OVERAN KGW 60,000 WIN AS OVERAN KGW 75,000 YB100 AS OVERAN KGW 95,000 A100 AS SHOCK DEPAN KGW 165,000 CB100K3 AS SHOCK DEPAN KGW 165,000 FORCE-1 AS SHOCK DEPAN KGW 150,000 GL-PRO AS SHOCK DEPAN KGW 167,500 OK here an example... On the invoice, brand is bery important.. diff brand .. difference price. 3 different type of drop down list... can u help? of coz the price is easy.. just have to use vlookup =) -- 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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
Hi
Just set up your 3 lists for 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, exhaust pipe, regulator, etc) as per the examples on the site I pointed you to. Apply Data Validation to column 1 to use the Motorcycle Name, to column 2 to use the Brand list and column 3 the Parts list. Make your selection from each list, and use the lookup formula to find the price. If the price is zero, then that particular item doesn't exist in your ;list, so try selecting a different brand. -- Regards Roger Govier "Hero_honda" wrote in message ... A100 AS OVERAN FMS 11,500 V80 AS OVERAN FMS 11,500 WIN AS OVERAN FMS 11,500 YB100 AS OVERAN FMS 11,500 A100 AS SHOCK DEPAN FMS 65,000 CB100K3 AS SHOCK DEPAN FMS 65,000 FORCE-1 AS SHOCK DEPAN FMS 50,000 GL-PRO AS SHOCK DEPAN FMS 67,500 SMASH AS OVERAN KGW 50,000 V80 AS OVERAN KGW 60,000 WIN AS OVERAN KGW 75,000 YB100 AS OVERAN KGW 95,000 A100 AS SHOCK DEPAN KGW 165,000 CB100K3 AS SHOCK DEPAN KGW 165,000 FORCE-1 AS SHOCK DEPAN KGW 150,000 GL-PRO AS SHOCK DEPAN KGW 167,500 OK here an example... On the invoice, brand is bery important.. diff brand .. difference price. 3 different type of drop down list... can u help? of coz the price is easy.. just have to use vlookup =) -- 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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Vlookup Problems Help!
hey roger, can i have: Column 1 = brand Column 2 = motorcycle name Column 3 = Motorcycle parts or Column 1 = motorcycle name column 2 = motorcycle parts column 3 = brand i find brand on the middle is hard for my invoice... i been to the site u gave me.. and i did try to test it... im almost getting 100% correct.. but not yet... i got some problems .. for example... column 1 = brand column 2 = parts of the motorcycle column 3 = name of the motorcycle however, i think excel needs column 3 to be A to Z order ... but i cannot do it becoz otherwise it will messed up my price list system. Brand Parts name FMS AXLE KICK A100 FMS HEAD LAMP CB100 FMS LEVER KLOS GL100 FMS PISTON PRIMA KGW CARBON BRUSH A100 KGW CARBON BRUSH GL100 KGW EXHAUST SUPRA KGW HEAD LAMP GL100 KGW LEVER KLOS CB100 KGW LEVER REM GL100 WHEN I select KGW, lever rem.... the dropdown list gives me GL100 and prima. it should be CB100. if you find the solution to this.. please try to help me .. If possible .. i want : Column 1 = motorcycle name column 2 = motorcycle parts column 3 = brand or Column 1 = brand Column 2 = motorcycle name Column 3 = Motorcycle parts -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Problems with HYPERLINK in Excel 2002 and 2003 | Excel Worksheet Functions | |||
Excel Page Range Printing Problems | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Problems pasting images into Excel | Excel Discussion (Misc queries) |