and statements returning a value

[ajp]

I have a date in Col A and a unique reference in colB, Value in ColE.
In col C I have another date and in col D unique if both agree (a=c and B=D) can you show in col F the result for E else "N/A"

thanks guys

 

[Aladin Akyurek]

=IF((A2=C2)*(B2=D2),E2,"N/A")

 

[Andrew Poulsom]

Try:

=IF(AND(A1=C1,B1=D1),E1,NA())

 

[ajp]

Sorry I was not specific enough

If think in English it should be

Where A=D and B=E then show C in Col F

(note that A & B & C will be on the same row. D & E & F will also be on the same row not necessarly the same row as A,B,C)

 

[Aladin Akyurek]

:wink:

Does this mean you don't understand the first reply?

 

[ajp]

Sorry I was not specific enough

If think in English it should be

Where A=D and B=E then show C in Col F

(note that A & B & C will be on the same row. D & E & F will also be on the same row not necessarly the same row as A,B,C)

 

[Aladin Akyurek]

Sorry I was not specific enough

If think in English it should be

Where A=D and B=E then show C in Col F

(note that A & B & C will be on the same row. D & E & F will also be on the same row not necessarly the same row as A,B,C)

Now, you lost me with that remark between parens. Can you give an example that depicts what you're after?

 

[ajp]

Columns A,B,C are (reference, date and a value). We also have in Col D and E references and dates but are looking for the value in col C to be shown in Col F where the reference in Col A equals the ref in Col D and the date in Col B equals the date in Col E

How do I add an example from the spreadsheet to show you??

 

[Aladin Akyurek]

Columns A,B,C are (reference, date and a value). We also have in Col D and E references and dates but are looking for the value in col C to be shown in Col F where the reference in Col A equals the ref in Col D and the date in Col B equals the date in Col E

The formulas given to you earlier appear to fit the above problem description...

=IF((A2=D2)*(B2=E2),C2,#N/A)

or

=IF((A2=D2)*(B2=E2),C2,"")

The * bit is the same as having a surrounding AND.

How do I add an example from the spreadsheet to show you??

Using HtmlMaker from

http://www.interq.or.jp/sun/puremis/colo/HtmlMaker.htm

 

[just_jon]

Sorry I was not specific enough

If think in English it should be

Where A=D and B=E then show C in Col F

(note that A & B & C will be on the same row. D & E & F will also be on the same row not necessarly the same row as A,B,C)

Aladin, I'm intuiting again -- I think what is desired is to check A1/B1 against *all* rows where A1=Dx and B1=Ex, then have C1 equal to Fx, x being row 2:x. Perhaps in C1, dragged down --

=SUMPRODUCT((D$1:D$500=A1)*(E$1:E$500=B1)*(F$1:F$500))