If and Match formula problem

[gcot]

Hi all,

I have a problem that would seem simple enough but I haven't been able to figure it out.

What I want to do is have a formula for each Item code row which represents orders for various items. This formula would check the Max Qty table and apply the max qty if the order qty was over the max otherwise it would keep the original amount. I hope I explained it clearly enough.

Thanks for your help,

Gilles

Item Code Qty
Item A 5
Item B 6
Item C 7
Item D 8


Item Code Max Qty
Item A 5
Item B 5
Item C 5
Item D 5

 

[fairwinds]

Hi,

How about this?

Book1
	A	B	C	D	E
1	Item	Code	Qty
2	Item	A	5		5
3	Item	B	6		5
4	Item	C	7		5
5	Item	D	4		4
6
7
8	Item	Code	MaxQty
9	Item	A	5
10	Item	B	5
11	Item	C	5
12	Item	D	5
Sheet2

 

[gcot]

Thanks fairwinds. You've come through for me again. It works.

Have a great day!

Gilles