A new Lookup Question

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
128
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.

Code:
[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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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.
 

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
128
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
 

Forum statistics

Threads
1,081,575
Messages
5,359,711
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top