# A new Lookup Question

#### wtom0412

##### Board Regular
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
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

##### MrExcel MVP
Sorry, F1 should be 1501, not 15001

M.

#### wtom0412

##### Board Regular
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

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

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