A new Lookup Question

[wtom0412]

Hi All,

This is similar to a previous question I asked, but the table I needed to create for the other option was becoming way too large and cumbersome and the lookup number had to be in multiples of 1000.

Here is my dilemma...

I am creating a label quote system.

There are 10 Groups of labels, and the quantity break is in lots of 500.

So, quantity breaks are 1-500, 501-1000, 1001-1500, 1501-2000 etc all the way up to 49501-50000

The quantity of labels could be any thing between 1 and 50000.

The layout of the table is...

Column A has Group Numbers (1 in A2, 2 in A3 ... 10 in A11)
Column B has Label Quantity (for example, 450 labels in B1, 1650 labels in B2 etc)
Columns C to CZ has the dollar value associated with Quantity - for example E5 = $0.637 which is the price per label for 1350 labels from Group 4.

[TABLE="width: 487"]
<tbody>[TR]
[TD][/TD]
[TD]   A[/TD]
[TD]       B[/TD]
[TD]      C[/TD]
[TD]     D[/TD]
[TD]      E[/TD]
[TD]     F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Group[/TD]
[TD]Label QNTY[/TD]
[TD]   0-500[/TD]
[TD] 501-1000[/TD]
[TD][B] 1001-1500[/B][/TD]
[TD] 1501-2000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]   1[/TD]
[TD]    450[/TD]
[TD]   0.498[/TD]
[TD]    0.495[/TD]
[TD]    0.490[/TD]
[TD]    0.485[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]   2[/TD]
[TD]    1650[/TD]
[TD]   0.550[/TD]
[TD]    0.545[/TD]
[TD]    0.539[/TD]
[TD]    0.534[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]   3[/TD]
[TD]    2500[/TD]
[TD]   0.600[/TD]
[TD]    0.594[/TD]
[TD]    0.588[/TD]
[TD]    0.582[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][B]   4[/B][/TD]
[TD]   1350[/TD]
[TD]   0.650[/TD]
[TD]    0.644[/TD]
[TD][B]    0.637[/B][/TD]
[TD]    0.631[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]   5[/TD]
[TD]    45200[/TD]
[TD]   0.700[/TD]
[TD]    0.693[/TD]
[TD]    0.686[/TD]
[TD]    0.679[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]   6[/TD]
[TD]    12600[/TD]
[TD]   0.750[/TD]
[TD]    0.743[/TD]
[TD]    0.735[/TD]
[TD]    0.728[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]   7[/TD]
[TD]    3420[/TD]
[TD]   0.800[/TD]
[TD]    0.792[/TD]
[TD]    0.784[/TD]
[TD]    0.776[/TD]
[/TR]
</tbody>[/TABLE]

I could do a nested IF lookup, but it would be huge!!

The logic is
IF(Group 1 Label QNTY is between 1 and 500 then Price = value in C2)
IF(Group 1 Label QNTY is between 501 and 1000 then Price = value in D2)
.
.
.
IF(Group 1 Label QNTY is between 49501 and 50000 then Price = value in CZ2)

I am sure there would be neat formula than can do the appropriate lookup for me, but it is really doing my head in!!

Any help would be greatly appreciated.

Cheers, WT

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Group​	Label QNTY​	0​	501​	1001​	15001​		Price​
2​	1​	450​	0,498​	0,495​	0,490​	0,485​		0,498​
3​	2​	1650​	0,550​	0,545​	0,539​	0,534​		0,539​
4​	3​	2500​	0,600​	0,594​	0,588​	0,582​		0,588​
5​	4​	1350​	0,650​	0,644​	0,637​	0,631​		0,637​
6​	5​	45200​	0,700​	0,693​	0,686​	0,679​		0,679​
7​	6​	12600​	0,750​	0,743​	0,735​	0,728​		0,735​
8​	7​	3420​	0,800​	0,792​	0,784​	0,776​		0,784​

<tbody>
</tbody>

Note that C1:F1 contains only the lower value (number) of each track.

Formula in H2 copied down
=LOOKUP(B2,C$1:F$1,C2:F2)

M.

 

[Marcelo Branco]

Sorry, F1 should be 1501, not 15001

M.

 

[wtom0412]

Thank you so much Marcelo,

A very nice elegant solution.

The more I think I know about Excel, the more I realise I have to learn!!

Cheers, WT