# 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 & &#62;=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

##### Member
Dear Smisar,

[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

Hui

Thanks a lot!

Best Regards,

Muneer

#### smisar

##### New Member

Item Qty Price 1 2 3 4 5 6-10 11-15 &#62;=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

#### smisar

##### New Member
Many thanks Fasseh...

God bless you...

Bye...

#### Faseeh

##### Excel Ninja
You are welcome smisar,

Regards,

Faseeh