Help with Nested IF and VLOOKUP

[samantham]

I need to confirm that the numbers in Column A (ACORD #) appear in Column E (IMS #). If they DO appear, I then need to compare the date in Column B (Current Version Date) to Column F (Version). If Column B is more recent than Column F, I need a result of "UPDATE NEEDED." If Column B and Column F match, I need a result of "no action necessary." If a number appears in Column A and is NOT in Column E, I need a result of "Form Missing," and if a number appears in Column E and is NOT in Column A, I need a result of "Unknown Form."

I know I need to do a Nested IF with VLookup, but I'm having issues with getting it to work as desired. Can anyone provide some insight on what I should be doing here? The final list has over 900 records, so any help in getting this figured out would be greatly appreciated!

THEIR #	Current Version Date	Future Version Date		OUR #	Version
50AL	2007/10			50AL	2007/10
62AL	1998/05			62AL	1998/05
90AL	2015/12			90AL	2010/11
137AL	2015/12			137AL	2010/11
138AL	2015/12			138AL	2012/04
290AL	2014/12			291AL	2010/11

 

[CA_Punit]

Is there alway the Alphabet "A" After Number???

 

[samantham]

There is not. The form numbers range from 1 - 951, and some (many) forms have a different version for different states, depending on state regulations (137, 137AL, 137NJ, 137TN, etc.).

 

[Norie]

For the first part try this in column C.

=IF(ISNUMBER(MATCH(A2,$E$2:$E$7,0)),IF(DATEVALUE(B2&"/1")>DATEVALUE(VLOOKUP(A2, $E$2:$F$7,2,0)&"/1"),"UPDATE NEEDED","No action necessary"), "Form Missing")

For the second part try this in column G.

=IF(ISNUMBER(MATCH(E2,$A$2:$A$7,0)), "", "Unknown form")

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I
9	THEIR #	Current Version Date	Helper Date	Future Version Date		OUR #	Version	Version Date
10	50AL	2007/10	01/10/2007	No Action Needed		50AL	2007/10	01/10/2007
11	62AL	1998/05	01/05/1998	No Action Needed		62AL	1985/05	01/05/1985
12	90AL	2015/12	01/12/2015	No Action Needed		90AL	2010/11	01/11/2010
13	137AL	2015/12	01/12/2015	No Action Needed		137AL	2010/11	01/11/2010
14	138AL	2015/12	01/12/2015	No Action Needed		138AL	2012/04	01/04/2012
15	290AL	2014/12	01/12/2014	Form Missing		291AL	2010/11	01/11/2010	Unknown Form
Sheet1

Cell Formulas
Range	Formula
C10:C15,H10:H15	C10	=DATE(MID(B10,1,4)+0,RIGHT(B10,2),1)
D10:D14	D10	=IF(AGGREGATE(15,6,SEARCH(CONCAT(IFERROR(MID(A10,ROW($1:$6),1)+0,""))+0,$F$10:$F$15)^0*$H$10:$H$15,1)>C10,"Update Necessary","No Action Needed")
D15	D15	=IFERROR(IF(AGGREGATE(15,6,SEARCH(CONCAT(IFERROR(MID(A15,ROW($1:$6),1)+0,""))+0,$F$10:$F$15)^0*$H$10:$H$15,1)>C15,"Update Necessary","No Action Needed"),"Form Missing")
I10:I15	I10	=IF(SUM(--ISNUMBER(SEARCH(CONCAT(IFERROR(MID(F10,ROW($1:$6),1)+0,""))+0,$A$10:$A$15)))=0,"Unknown Form","")

 

[CA_Punit]

I Didnt know how to convert the Date Column so i have used Helper Column


I have only considered the number in 50AL i.e 50, If this is not the case for your requirement the formula will be little simple

 

[CA_Punit]

In case you require details to be matched as it is i.e 50AL, then

Book1
	A	B	C	D	E	F	G	H	I
9	THEIR #	Current Version Date	Helper Date	Future Version Date		OUR #	Version	Version Date
10	50AL	2007/10	01/10/2007	No Action Needed		50AL	2007/10	01/10/2007
11	62AL	1998/05	01/05/1998	No Action Needed		62AL	1985/05	01/05/1985
12	90AL	2015/12	01/12/2015	No Action Needed		90AL	2010/11	01/11/2010
13	137AL	2015/12	01/12/2015	No Action Needed		137AL	2010/11	01/11/2010
14	138AL	2015/12	01/12/2015	No Action Needed		138AL	2012/04	01/04/2012
15	290AL	2014/12	01/12/2014	Form Missing		291AL	2010/11	01/11/2010	Unknown Form
Sheet1

Cell Formulas
Range	Formula
C10:C15,H10:H15	C10	=DATE(MID(B10,1,4)+0,RIGHT(B10,2),1)
D10:D15	D10	=IFERROR(IF(VLOOKUP(A10,IF({1,0},$F$10:$F$15,$H$10:$H$15),2,0)>C10,"Update Necessary","No Action Needed"),"Form Missing")
I10:I15	I10	=IF(SUM(--ISNUMBER(SEARCH(F10,$A$10:$A$15)))=0,"Unknown Form","")