A question about IF AND?

pingvin

New Member
Joined
Oct 28, 2017
Messages
15
Hi,

I have a work book with two sheets.

On the Sheet1
I have a table with data for Restaurant in cells A1 to I4
Then I have data for Super market in cells A6 to I9
Finally I have data for Coffee shop in cells A11 to I14
A
B
C
D
E
F
G
H
I
1
Restaurant
Discount offer
Low
Medium
High
2
Min
Max
Min
Max
Min
Max
Min
Max
3
Price
1
300
301
600
601
1000
1001
60000
4
Gross Margin
30%
100%
55%
100%
50%
100%
45%
100%
5
6
Super Market
Discount offer
Low
Medium
High
7
Min
Max
Min
Max
Min
Max
Min
Max
8
Price
1
300
301
600
601
1000
1001
60000
9
Gross Margin
1%
10%
11%
35%
35%
100%
40%
100%
10
11
Coffee shop
Discount offer
Low
Medium
High
12
Min
Max
Min
Max
Min
Max
Min
Max
13
Price
1
300
301
600
601
1000
1001
60000
14
Gross Margin
45%
100%
50%
100%
55%
100%
55%
100%

<tbody>
</tbody>



On Sheet2
I have another table
B
C
D
E
F
G
H
I
J
K
3
Price Category:
Discount offer
4
Area:
Restaurant
5
6
7
Item Code
Supplier
Item
Unit
Std.Cost
QTY(g)
Act QTY
Act Cost
8
1
20001
Supplier A
Spaghetti
Bags
2
1000
1
2.0
9
2
10
3
11
4
12
5
13
6
14
7
15
8
16
9
17
10
18
11
19
12
20
13
21
14
22
15
23
24
Price
GM
25
$10.00
80%

<tbody>
</tbody>


Based on the value in cell D4 and J25 in Sheet2 I want the update the cell value in D3 on sheet2.
For example if the value in D4 is "Restaurant" and the cell value in J25 is "$10.00" I want it to get the value from cell B1 "Discount offer" on Sheet1 and show this in cell D3 on sheet2. Because the value in D4 states restaurant which means that the values located somewhere between cell A1 to I4. Further more since the price in cell J25 is $10.00 which falls between the values in cell B3 to C3 on sheet1. Cell D3 on sheet2 should show " Discount offer".

Next is "GM" in cell K25. If this falls between the "Discount offer Min and Max" values nothing should happen but if it falls out of the Min and Max the cell should be highlighted in red color.
I pasted a example file which basically shows the same as I have described.

If the Value in D4 for example should show "Coffee shop" then the value for D3 should be found somewhere inbetween cell A11 and I14 on sheet1. The gross margin value in cell J25 should be matched with the values in between B14 to I14, if values matches the between the Min and Max values nothing should change but if the values falls out of the Min Max the cell color changes to red.

Below is the link to the file. Thanks in advance!
https://1drv.ms/x/s!AgXqCHJekjZxapUdFYClVY3qxZc


 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,148
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this

formula for CF J25 & K25

Code:
=$K$25 < INDEX(Sheet1!$A$1:$I$14,MATCH($D$4,Sheet1!$A$1:$A$14,0)+3,MATCH($D$3,Sheet1!$A$1:$I$1,0))
https://drive.google.com/file/d/1K-MaQUiPSVKVKCKJ4dgQwodTS9p5U9d6/view?usp=sharing

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #808080;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;color: #808080;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;color: #808080;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;color: #808080;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;color: #808080;background-color: #FFFFFF;;">Price category:</td><td style=";">Low</td><td style="color: #808080;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;color: #808080;background-color: #FFFFFF;;">Area:</td><td style=";">Restaurant</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;color: #808080;background-color: #FFFFFF;;"></td><td style="color: #808080;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;color: #808080;background-color: #FFFFFF;;"></td><td style="color: #808080;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;color: #808080;background-color: #FFFFFF;;"></td><td style="text-align: center;color: #808080;background-color: #FFFFFF;;"></td><td style="text-align: right;color: #808080;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #808080;;"></td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #808080;;">Item Code</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #808080;;">Supplier</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #808080;;">Item</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #808080;;">Unit</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #808080;;">Std.Cost</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #808080;;">QTY (g)</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #808080;;">Act Qty</td><td style="border-bottom: 1px solid black;border-left: 1px solid black;color: #FFFFFF;background-color: #808080;;">Act Cost</td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">1</td><td style="text-align: right;border-top: 1px solid black;;">20001</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">Supplier A</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">Spaghetti</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">Bags</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;">1000</td><td style="text-align: right;border-top: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;">2.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #808080;;">15</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0.0</td><td style="text-align: right;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #808080;;">Sub Total</td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #808080;;"></td><td style="text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #808080;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #808080;;">SRP</td><td style="font-weight: bold;text-align: right;border-bottom: 1px solid black;color: #FFFFFF;background-color: #808080;;">GM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;color: #FFFFFF;background-color: #808080;;">1,000g</td><td style="text-align: right;border-top: 1px solid black;color: #FFFFFF;background-color: #808080;;">$2.00</td><td style="border-top: 1px solid black;color: #FFFFFF;background-color: #808080;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;color: #FFFFFF;background-color: #808080;;">$500.00</td><td style="text-align: right;border-top: 1px solid black;color: #FFFFFF;background-color: #808080;;">50%</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=LOOKUP(<font color="Blue">J25,{1;301;601;1001},{"Discount offer";"Low";"Medium";"High"}</font>)</td></tr></tbody></table></td></tr></table><br />
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,148
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
btw, the formula in D3 takes advantage of Rows 3, 8 & 11 on Sheet1 are identical as in your example file, otherwise it wouldn't work
 

pingvin

New Member
Joined
Oct 28, 2017
Messages
15
Thanks for the quick answer AlanY. Unfortunatly the prices on rows 3, 8 and 13 will differ depending on if it for Restaurant, Super market or Coffee shop. So I guess it won't work.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,633
Messages
5,523,995
Members
409,555
Latest member
TIPSAREA

This Week's Hot Topics

Top