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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Group​

[/TD]
[TD]

Label QNTY​

[/TD]
[TD]

0​

[/TD]
[TD]

501​

[/TD]
[TD]

1001​

[/TD]
[TD]

15001​

[/TD]
[TD][/TD]
[TD]

Price​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

450​

[/TD]
[TD]

0,498​

[/TD]
[TD]

0,495​

[/TD]
[TD]

0,490​

[/TD]
[TD]

0,485​

[/TD]
[TD][/TD]
[TD]

0,498​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

1650​

[/TD]
[TD]

0,550​

[/TD]
[TD]

0,545​

[/TD]
[TD]

0,539​

[/TD]
[TD]

0,534​

[/TD]
[TD][/TD]
[TD]

0,539​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

2500​

[/TD]
[TD]

0,600​

[/TD]
[TD]

0,594​

[/TD]
[TD]

0,588​

[/TD]
[TD]

0,582​

[/TD]
[TD][/TD]
[TD]

0,588​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

1350​

[/TD]
[TD]

0,650​

[/TD]
[TD]

0,644​

[/TD]
[TD]

0,637​

[/TD]
[TD]

0,631​

[/TD]
[TD][/TD]
[TD]

0,637​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

5​

[/TD]
[TD]

45200​

[/TD]
[TD]

0,700​

[/TD]
[TD]

0,693​

[/TD]
[TD]

0,686​

[/TD]
[TD]

0,679​

[/TD]
[TD][/TD]
[TD]

0,679​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

6​

[/TD]
[TD]

12600​

[/TD]
[TD]

0,750​

[/TD]
[TD]

0,743​

[/TD]
[TD]

0,735​

[/TD]
[TD]

0,728​

[/TD]
[TD][/TD]
[TD]

0,735​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

3420​

[/TD]
[TD]

0,800​

[/TD]
[TD]

0,792​

[/TD]
[TD]

0,784​

[/TD]
[TD]

0,776​

[/TD]
[TD][/TD]
[TD]

0,784​

[/TD]
[/TR]
</tbody>[/TABLE]


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