Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possbile for non-contiguous vlookup?
Good afternoon - I have a order entry file I'm trying to enable the
possibility for offering quantity breaks. However, the different items to be sold have different levels of qty breaks. For example: Item Qty Price Break1 Break1Price Break2 Break2 Price a 1 100 5 90 10 80 b 1 200 8 180 15 160 c 1 300 10 280 20 250 If the user enters the item, I am hoping to have it lookup the item, and choose the proper unit price based on the quantity offered. For example, 10 of item b would cost $180 each. My problem seems to be the non-contiguous setup of the qty breaks - i've tried named ranges, vlookup, index, and match functions, with/without named ranges - all unsuccessfully. Any ideas gurus? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possbile for non-contiguous vlookup?
Hi!
Your setup is not ideal! I would group all the Qty's and prices. Item......Q1.....Break1.....Break2...........Price ......Price1.....Price2 A............1..........5.............10.......... ......100..........90...........80 B............1..........8.............15.......... ......200.........180.........160 C............1.........10............20........... .....300.........280.........250 Here's a sample file. Sample_lookup2.xls 13.5kb http://cjoint.com/?joxRGgpo3U Biff wrote in message oups.com... Good afternoon - I have a order entry file I'm trying to enable the possibility for offering quantity breaks. However, the different items to be sold have different levels of qty breaks. For example: Item Qty Price Break1 Break1Price Break2 Break2 Price a 1 100 5 90 10 80 b 1 200 8 180 15 160 c 1 300 10 280 20 250 If the user enters the item, I am hoping to have it lookup the item, and choose the proper unit price based on the quantity offered. For example, 10 of item b would cost $180 each. My problem seems to be the non-contiguous setup of the qty breaks - i've tried named ranges, vlookup, index, and match functions, with/without named ranges - all unsuccessfully. Any ideas gurus? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possbile for non-contiguous vlookup?
Thanks gang, works perfectly! Nicely done -
Domenic wrote: While Biff's solution is much more efficient, here's one which uses the current layout... Assumptions: A2:G4 contains the data A10 contains the item of interest, such as b, and B10 contains the quantity, such as 10 Formula: =INDEX(B2:G4,MATCH(A10,A2:A4,0),MATCH(B10,IF(MOD(C OLUMN(INDEX(B2:G4,MATCH (A10,A2:A4,0),0))-COLUMN(B2),2)=0,INDEX(B2:G4,MATCH(A10,A2:A4,0),0)) )+1) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article .com, wrote: Good afternoon - I have a order entry file I'm trying to enable the possibility for offering quantity breaks. However, the different items to be sold have different levels of qty breaks. For example: Item Qty Price Break1 Break1Price Break2 Break2 Price a 1 100 5 90 10 80 b 1 200 8 180 15 160 c 1 300 10 280 20 250 If the user enters the item, I am hoping to have it lookup the item, and choose the proper unit price based on the quantity offered. For example, 10 of item b would cost $180 each. My problem seems to be the non-contiguous setup of the qty breaks - i've tried named ranges, vlookup, index, and match functions, with/without named ranges - all unsuccessfully. Any ideas gurus? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possbile for non-contiguous vlookup?
Domenic, thanks for your help - can I ask for one more piece of advice?
this works great when every part number has three sets of qty breaks. However, it zeroes out when an item does not have any qty break. So, to change the original post, item c will zero out, even for a qty of 1, as follows: Item Qty Price Break1 Break1Price Break2 Break2 Price A 1 100 5 90 10 80 B 1 200 C 1 300 10 280 20 250 What should I change in your formula for this to work? (Basically, in other words, the price for item B, regardless of qty, should be $200) Thanks, Brian Domenic wrote: While Biff's solution is much more efficient, here's one which uses the current layout... Assumptions: A2:G4 contains the data A10 contains the item of interest, such as b, and B10 contains the quantity, such as 10 Formula: =INDEX(B2:G4,MATCH(A10,A2:A4,0),MATCH(B10,IF(MOD(C OLUMN(INDEX(B2:G4,MATCH (A10,A2:A4,0),0))-COLUMN(B2),2)=0,INDEX(B2:G4,MATCH(A10,A2:A4,0),0)) )+1) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article .com, wrote: Good afternoon - I have a order entry file I'm trying to enable the possibility for offering quantity breaks. However, the different items to be sold have different levels of qty breaks. For example: Item Qty Price Break1 Break1Price Break2 Break2 Price a 1 100 5 90 10 80 b 1 200 8 180 15 160 c 1 300 10 280 20 250 If the user enters the item, I am hoping to have it lookup the item, and choose the proper unit price based on the quantity offered. For example, 10 of item b would cost $180 each. My problem seems to be the non-contiguous setup of the qty breaks - i've tried named ranges, vlookup, index, and match functions, with/without named ranges - all unsuccessfully. Any ideas gurus? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Possbile for non-contiguous vlookup?
Try...
=INDEX(B2:G4,MATCH(A10,A2:A4,0),MATCH(B10,IF(MOD(C OLUMN(INDEX(B2:G4,MATCH (A10,A2:A4,0),0))-COLUMN(B2),2)=0,IF(INDEX(B2:G4,MATCH(A10,A2:A4,0), 0)<" ",INDEX(B2:G4,MATCH(A10,A2:A4,0),0))))+1) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article . com, wrote: Domenic, thanks for your help - can I ask for one more piece of advice? this works great when every part number has three sets of qty breaks. However, it zeroes out when an item does not have any qty break. So, to change the original post, item c will zero out, even for a qty of 1, as follows: Item Qty Price Break1 Break1Price Break2 Break2 Price A 1 100 5 90 10 80 B 1 200 C 1 300 10 280 20 250 What should I change in your formula for this to work? (Basically, in other words, the price for item B, regardless of qty, should be $200) Thanks, Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |