• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Price as per quantity

smisar

New Member
I have part number column & prices in columns based on quantity such as 1,2,3,4,5 6~10, 11~15 & >=16 etc.


i wish to have price appeared for exact quantity... for that part number...


when I type qty in blank column, the right price for that quantity should appear in next blank column..


can anyone help....
 
Dear Smisar,

According to your example, think your data A1:D17

[pre]
Code:
Part   Qty     Qty	Rate Per
Number From    To       Unit
111	1	1	10
111	2	2	9.5
111	3	3	9
111	4	4	8.5
111	5	5	8
111	6	10	7.5
111	11	15	7
111	16	Above	6.5
222	1	1	11
222	2	2	10.5
222	3	3	10
222	4	4	9.5
222	5	5	9
222	6	10	8.5
222	11	15	8
222	16	Above	7.5
[/pre]
Type G2=222 and H2=7 and

I2=VLOOKUP(G2,OFFSET(A2,MATCH(G2,A2:A17,0)-1,0,MATCH(H2,B10:B17,1),4),4)

It will return 7.5


Regards,


Muneer
 

Hui

Excel Ninja
Staff member
Muneer


When adding tables like yours above

Please insert a single ` before and after the table

that way Tabs are maintained.


That is the character next to the 1 key (under the Esc kay) not next to the Enter Key
 

smisar

New Member
Dear Muneer thanks for your reply... but it is little different.


Item Qty Price 1 2 3 4 5 6-10 11-15 >=16


abc 10 43 100 80 70 65 58 43 39 32

pqr 4 63 98 78 68 63 55 40 35 29


abc 3 70 100 80 70 65 58 43 39 32

pqr 12 35 98 78 68 63 55 40 35 29


Hope you will understand and solve my query, please..

regards

Sanjay Misar
 

Faseeh

Excel Ninja
Hi smisar,


Assuming your data in A1:K5, like this:

[pre]
Code:
Item	Qty	Price	1	2	3	4	5	6	11	16
abc	15	39	100	80	70	65	58	43	39	32
pqr	4	63	98	78	68	63	55	40	35	29
abc	3	70	100	80	70	65	58	43	39	32
pqr	12	35	98	78	68	63	55	40	35	29
[/pre]
Enter this in C2 under Price and drag down:


=LOOKUP(B2,$D$1:$K$1,D2:K2)


Regards,

Faseeh
 
Top