A question about IF AND?

[pingvin]

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
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Restaurant
[/TD]
[TD]Discount offer
[/TD]
[TD][/TD]
[TD]Low
[/TD]
[TD][/TD]
[TD]Medium
[/TD]
[TD][/TD]
[TD]High
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Price
[/TD]
[TD]1
[/TD]
[TD]300
[/TD]
[TD]301
[/TD]
[TD]600
[/TD]
[TD]601
[/TD]
[TD]1000
[/TD]
[TD]1001
[/TD]
[TD]60000
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Gross Margin
[/TD]
[TD]30%
[/TD]
[TD]100%
[/TD]
[TD]55%
[/TD]
[TD]100%
[/TD]
[TD]50%
[/TD]
[TD]100%
[/TD]
[TD]45%
[/TD]
[TD]100%
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Super Market
[/TD]
[TD]Discount offer
[/TD]
[TD][/TD]
[TD]Low
[/TD]
[TD][/TD]
[TD]Medium
[/TD]
[TD][/TD]
[TD]High
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Price
[/TD]
[TD]1
[/TD]
[TD]300
[/TD]
[TD]301
[/TD]
[TD]600
[/TD]
[TD]601
[/TD]
[TD]1000
[/TD]
[TD]1001
[/TD]
[TD]60000
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Gross Margin
[/TD]
[TD]1%
[/TD]
[TD]10%
[/TD]
[TD]11%
[/TD]
[TD]35%
[/TD]
[TD]35%
[/TD]
[TD]100%
[/TD]
[TD]40%
[/TD]
[TD]100%
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Coffee shop
[/TD]
[TD]Discount offer
[/TD]
[TD][/TD]
[TD]Low
[/TD]
[TD][/TD]
[TD]Medium
[/TD]
[TD][/TD]
[TD]High
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Min
[/TD]
[TD]Max
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Price
[/TD]
[TD]1
[/TD]
[TD]300
[/TD]
[TD]301
[/TD]
[TD]600
[/TD]
[TD]601
[/TD]
[TD]1000
[/TD]
[TD]1001
[/TD]
[TD]60000
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]Gross Margin
[/TD]
[TD]45%
[/TD]
[TD]100%
[/TD]
[TD]50%
[/TD]
[TD]100%
[/TD]
[TD]55%
[/TD]
[TD]100%
[/TD]
[TD]55%
[/TD]
[TD]100%
[/TD]
[/TR]
</tbody>[/TABLE]



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


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

 

[AlanY]

try this

formula for CF J25 & K25

=$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

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3			Price category:	Low
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								0.0
10		3								0.0
11		4								0.0
12		5								0.0
13		6								0.0
14		7								0.0
15		8								0.0
16		9								0.0
17		10								0.0
18		11								0.0
19		12								0.0
20		13								0.0
21		14								0.0
22		15								0.0
23
24						Sub Total				SRP	GM
25						1,000g	$2.00			$500.00	50%
Sheet2

Cell Formulas
Range	Formula
D3	=LOOKUP(J25,{1;301;601;1001},{"Discount offer";"Low";"Medium";"High"})

 

[AlanY]

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]

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.

 

[AlanY]

i'd modified the formula to suit your requirements and 2 extra cells L25 & L26 for the conditional format.
note that I'd changed the raw data on sheet1 for testing.

file here

https://drive.google.com/file/d/1T39rWDLPvpunVlNTggUtv-lpGSzs31Zl/view?usp=sharing