# A question about IF AND?

#### pingvin

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

https://1drv.ms/x/s!AgXqCHJekjZxapUdFYClVY3qxZc

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### AlanY

##### Well-known Member
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))``

Book1
ABCDEFGHIJK
1
2
3Price category:Low
4Area:Restaurant
5
6
7Item CodeSupplierItemUnitStd.CostQTY (g)Act QtyAct Cost
8120001Supplier ASpaghettiBags2100012.0
920.0
1030.0
1140.0
1250.0
1360.0
1470.0
1580.0
1690.0
17100.0
18110.0
19120.0
20130.0
21140.0
22150.0
23
24Sub TotalSRPGM
251,000g\$2.00\$500.0050%
Sheet2
Cell Formulas
RangeFormula
D3=LOOKUP(J25,{1;301;601;1001},{"Discount offer";"Low";"Medium";"High"})

#### AlanY

##### Well-known Member
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
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,827
Messages
5,855,877
Members
431,771
Latest member
CoryMelth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back