Using

[JoshuaZeno]

Hi there! I am currently trying to return a specific value in the "Result" column using "If" statements. In the picture, some policies are the same with the exception of the last three or two digits (003 or 03 is a different version of the same policy for 001 or 01 respectively) If the Subfund is the string "* BC Riders on UL" AND the COI Type is the string "N/A", I would like to return the COI Type of its original policy number. For example, with the conditions listed above, for Row 2 I would like "Result" to return its original policy COI Type (LEV). And in row 5, I would like "Result" to return YRT since this row satisfies the two string conditions above and thus will return the COI Type of its original (001 or 01) policy. Could someone help me produce this formula for the "Result" column? (Please ignore row 4, it's just there to show values are not the same in each column)

 

[JoshuaZeno]

Sorry about the title, it's supposed to be Using "If" Statements

 

[JamesCanale]

MrExcelPlayground (version 2).xlsb
	A	B	C	D
1	PolicyID	Subfund	COI Type	Result
2	099649446U 003	* BC Riders On UL	N/A	LEV
3	099649446U 001	* BC Riders On UL	LEV	What here?
4	H082394T 01	* BC Perm	YRT	What here?
5	H041466U 01	* BC Riders On UL	YRT	What here?
6	H041466U 03	* BC Riders On UL	N/A	YRT
Sheet48

Cell Formulas
Range	Formula
D2:D6	D2	=IF(AND(C2="N/A",B2="* BC Riders On UL"),VLOOKUP(LEFT(A2,LEN(A2)-1)&"1",$A$2:$C$6,3,FALSE),"What here?")