Help with Nested If, And, & Or Statements or somehow using a table w/ lookup functions ?

[Lynn0713]

Hello,

I'm by no means an expert in Excel, but I usually can get by. However, I am trying to figure out the best / easiest way to insert the insurance company based off the Date in Column A and the Loss Type in Column V. I would like the result (the insurance company) to go in Column U)

Here is what I have to try and explain my logic on this.

Claim Type - Column V	DOL Range - Column A	Insurance Company
PD Ins.	Before 3/1/2016	Ins. Co. A
PD	Before 3/1/2016	Ins. Co. C
BI	Before 3/1/2016	Ins. Co. A
Cargo Ins.	Before 3/1/2015	Ins. Co. B
Cargo Ins.	After 3/1/2015	Ins. Co. E
Cargo	Anytime	Ins. Co. C
VPD Ins.	Before 3/1/2015	Ins. Co. B
VPD Ins.	After 3/1/2015	Ins. Co. F
VPD	Anytime	Ins. Co. C
Accident	Anytime	Ins. Co. C
Incident	Anytime	Ins. Co. C
PD	After 3/1/2016	Ins. Co. B
BI	After 3/1/2016	Ins. Co. B

<tbody>
</tbody>

IF A2< “3/1/2016” AND V2= “PD Ins.” OR V2= “BI”, Ins. Co. A
IF A2>= “3/1/2016” AND V2= “PD” OR V2= “BI”, Ins. Co. B
IF A2< “3/1/2016” AND V2 = “PD”, Ins. Co. C
IF A2< “3/1/2015” AND V2= “Cargo Ins.”, Ins. Co. D
IF A2>= “3/1/2015” AND V2= “Cargo Ins.”, Ins. Co. E
IF A2< “3/1/2015” AND V2= “VPD Ins.”, Ins. Co. D
IF A2>= “3/1/2015” AND V2= “VPD Ins.”, Ins. Co. F
IF OR V2= “VPD”, “Cargo”, “Accident”, “Incident”, Ins. Co. C




Here is a small portion of an example spreadsheet (I did remove some columns for the example below, so the "insurer" isn't in column U below & loss type isn't in V2 below, but that is the column in the actual spreadsheet):

DOL Office Company City State Ins.? Police Report Cargo Claim Open VPD Claim Open PD Claim Open BI Claim Open Total # of Claims Loss Type Insurer 12/19/11 ETKS ETKS Jacksonville FL Y X N N N Y 2 Incident 12/19/11 ETKS ETKS Jacksonville FL Y X Y 2 BI 12/19/11 ETKS ETKS Jacksonville FL Y X N 2 PD Ins. 6/3/14 TPFL ETC Cantonment FL Y Y N N N N 4 Accident 6/3/14 TPFL ETC Cantonment FL Y Y N 4 BI 6/3/14 TPFL ETC Cantonment FL Y Y N 4 BI 6/3/14 TPFL ETC Cantonment FL Y Y N 4 BI 6/3/14 TPFL ETC Cantonment FL Y Y N 4 PD Ins. 9/15/14 RDGA RPS Decatur GA Y Y N N N Y 3 Accident 9/15/14 RDGA RPS Decatur GA Y Y N 3 VPD Ins. 9/15/14 RDGA RPS Decatur GA Y Y Y 3 BI 9/15/14 RDGA RPS Decatur GA Y Y N 3 PD Ins. 10/8/14 SKFL RPS Callahan FL Y Y N N N Y 3 Accident 10/8/14 SKFL RPS Callahan FL Y Y Y 3 BI 10/8/14 SKFL RPS Callahan FL Y Y Y 3 BI <tbody> </tbody>

<tbody>
</tbody>

Thank all of you in advance for your assistance, knowledge, and expertise! I do appreciate it.

 

[Krayons]

=IF(condition, true, false)

=IF(condition, true, IF(condition, true, false))

=IF(condition, true, IF(condition, true, IF(condition, true, false)))

=IF(condition, true, IF(condition, true, IF(condition, true, IF(condition, true, false))))

Adding that much into an IF function can, as you can see, get confusing very quickly.

I would probably store some constants in hidden fields and reference those just to make it a little easier to write.

EG: Instead of writing something like:

IF(A2<“3/1/2016”, true, false)

You can do something like:

Z1 = 3/1/2016

IF(A2< Z1, true, false)

Good luck.

 

[FDibbins]

Agree that nesting IF's to any depth can become problematic to keep track of.

I would suggest putting together a small(?) table that would have the dates and codes, and contain the company, something like this...

	A​	B​	C​	D​
1​		3/1/2015​	3/1/2016​	3/1/2017​
2​	PD Ins	aa	ff	kk
3​	BI	bb	gg	ll
4​	PD	cc	hh	mm
5​	Cargo Ins	dd	ii	nn
6​	VPD Ins	ee	jj	oo
7​
8​
9​
10​	gg	4/1/2016​	BI

A10=INDEX($B$2:$D$6,MATCH(C10,$A$2:$A$6,0),MATCH(B10,$B$1:$D$1,1))

 

[Lynn0713]

Thank you so much, this worked perfectly!

I made the table with the dates, loss types, and insurance companies, used your formula, and it did it perfectly! I'm currently working with a small sample data of 231 rows - the project I have to finish has closer to 1500 rows, so this really helped me out!

Thank you again.

Agree that nesting IF's to any depth can become problematic to keep track of.

I would suggest putting together a small(?) table that would have the dates and codes, and contain the company, something like this...

	A​	B​	C​	D​
1​		3/1/2015​	3/1/2016​	3/1/2017​
2​	PD Ins	aa	ff	kk
3​	BI	bb	gg	ll
4​	PD	cc	hh	mm
5​	Cargo Ins	dd	ii	nn
6​	VPD Ins	ee	jj	oo
7​
8​
9​
10​	gg	4/1/2016​	BI

<tbody>
</tbody>

A10=INDEX($B$2:$D$6,MATCH(C10,$A$2:$A$6,0),MATCH(B10,$B$1:$D$1,1))