Hi Dan

With Gift etc. in column A, and value in column B enter in C2
=IF(A2="Membership",B2,IF(A2="GIFT",MIN(40,--(B2*ISNUMBER(B2))),""))

The ISNUMBER bit is to ensure that a blank value in B, with Gift in A
doesn't get picked up as 40.

Regards

Roger Govier


Dan L. wrote:
I'm doing a spreadsheet and would like some help. I want the foumula to
comapre one cell and if it has the gift in it and the next cell over is below
40 then the value of the cell if over 40 then 40. Easy enough? no, I also
want if the first cell has the word membership in it then the value of the
next cell is the value.
Example
Cell 1 Amount claimed amount authorized
gift 45 40
gift 35 35
membership 25 25
membership 70 70

Please help!!!