Price as per quantity

[smisar]

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....

 

[nazmul_muneer]

Dear Smisar,

According to your example, think your data A1:D17

[pre]

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]

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

 

[nazmul_muneer]

Hui

Thanks a lot!


Best Regards,

Muneer

 

[smisar]

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]

Hi smisar,


Assuming your data in A1:K5, like this:

[pre]

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

 

[smisar]

Many thanks Fasseh...

God bless you...

Bye...

 

[Faseeh]

You are welcome smisar,

Regards,

Faseeh