# 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

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
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))``

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

Replies
5
Views
38
Replies
4
Views
83
Replies
3
Views
72
Replies
11
Views
702
Replies
2
Views
335