Possbile for non-contiguous vlookup?

[aimhigh1]

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?

 

[Biff]

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

 

[Domenic]

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(COLUMN(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!

 

[aimhigh1]

Thanks gang, works perfectly! Nicely done -

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(COLUMN(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!

 

[aimhigh1]

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]

Try...

=INDEX(B2:G4,MATCH(A10,A2:A4,0),MATCH(B10,IF(MOD(COLUMN(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!