Formula with 2 conditions

[tygrl510]

hi,

I have two worksheets. The first worksheet contains a case ID number and a incident date. The other worksheet has similar information but includes an event number along with other various columns. The sheets are not identical.

It returned an event number. But for cases with similar numbers, it returned only one value, instead of 2. For example:

Excel Workbook
	A	B	C
1	Case No.	Date of Incident	Event
2	001-001	9/15/2007	#1
3	001-001	10/13/2008	#2
4	001-001	1/10/2011	#2
5	001-002	5/5/2010	#2
Original File

#VALUE!

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(AND(A2,B2),VLOOKUP(A2,'Original File'!$A$1:$C$5,3),0)
C3	=IF(AND(A3,B3),VLOOKUP(A3,'Original File'!$A$1:$C$5,3),0)
C4	=IF(AND(A4,B4),VLOOKUP(A4,'Original File'!$A$1:$C$5,3),0)
C5	=IF(AND(A5,B5),VLOOKUP(A5,'Original File'!$A$1:$C$5,3),0)

[/B]
Can someone help with the formula?

Thanks in advance!!

 

[Yahya]

see if this is what you want
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Case No.</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Date of Incident</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Event</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">001-001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9/15/2007</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">001-001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10/13/2008</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">001-001</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">01/10/2011</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">001-002</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">05/05/2010</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">#2</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Trial</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">{=INDEX(<font color="Blue">'Original File'!$C$2:$C$5,MATCH(<font color="Red">1,IF(<font color="Green">'Original File'!$A$2:$A$5=A2,IF(<font color="Purple">'Original File'!$B$2:$B$5=B2,1</font>)</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Excelestial]

Concatenate your Case No. and date to create unique records...and run a VLOOKUP on the concatenated field.