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

L

Lynn0713

New Member
Joined
Feb 27, 2013
Messages
11
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 VDOL Range - Column AInsurance Company
PD Ins.Before 3/1/2016Ins. Co. A
PDBefore 3/1/2016Ins. Co. C
BIBefore 3/1/2016Ins. Co. A
Cargo Ins.Before 3/1/2015Ins. Co. B
Cargo Ins.After 3/1/2015Ins. Co. E
CargoAnytimeIns. Co. C
VPD Ins.Before 3/1/2015Ins. Co. B
VPD Ins.After 3/1/2015Ins. Co. F
VPDAnytimeIns. Co. C
AccidentAnytimeIns. Co. C
IncidentAnytimeIns. Co. C
PDAfter 3/1/2016Ins. Co. B
BIAfter 3/1/2016Ins. 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):


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


Thank all of you in advance for your assistance, knowledge, and expertise! I do appreciate it.
 
Last edited:
Sort by date Sort by votes
Code: 
=IF(condition, true, false)

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

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

Code: 
=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:

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

You can do something like:

Z1 = 3/1/2016
Code: 
IF(A2< Z1, true, false)

Good luck.
 
Last edited:
Upvote 0
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...

[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][/tr]
[tr][td]
1​
[/td][td][/td][td]
3/1/2015​
[/td][td]
3/1/2016​
[/td][td]
3/1/2017​
[/td][/tr]

[tr][td]
2​
[/td][td]PD Ins[/td][td]aa[/td][td]ff[/td][td]kk[/td][/tr]

[tr][td]
3​
[/td][td]BI[/td][td]bb[/td][td]gg[/td][td]ll[/td][/tr]

[tr][td]
4​
[/td][td]PD[/td][td]cc[/td][td]hh[/td][td]mm[/td][/tr]

[tr][td]
5​
[/td][td]Cargo Ins[/td][td]dd[/td][td]ii[/td][td]nn[/td][/tr]

[tr][td]
6​
[/td][td]VPD Ins[/td][td]ee[/td][td]jj[/td][td]oo[/td][/tr]

[tr][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]gg[/td][td]
4/1/2016​
[/td][td]BI[/td][td][/td][/tr]
[/table]
A10=INDEX($B$2:$D$6,MATCH(C10,$A$2:$A$6,0),MATCH(B10,$B$1:$D$1,1))
 
Upvote 0
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.

FDibbins said:
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...

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD][/TD]
[TD]
3/1/2015​
[/TD]
[TD]
3/1/2016​
[/TD]
[TD]
3/1/2017​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]PD Ins[/TD]
[TD]aa[/TD]
[TD]ff[/TD]
[TD]kk[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]BI[/TD]
[TD]bb[/TD]
[TD]gg[/TD]
[TD]ll[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]PD[/TD]
[TD]cc[/TD]
[TD]hh[/TD]
[TD]mm[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Cargo Ins[/TD]
[TD]dd[/TD]
[TD]ii[/TD]
[TD]nn[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]VPD Ins[/TD]
[TD]ee[/TD]
[TD]jj[/TD]
[TD]oo[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]gg[/TD]
[TD]
4/1/2016​
[/TD]
[TD]BI[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

A10=INDEX($B$2:$D$6,MATCH(C10,$A$2:$A$6,0),MATCH(B10,$B$1:$D$1,1))
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
formula to return two columns of data adjacent to #N/A cell entry
Replies
3
Views
411
Fluff
Fluff
M
  • Question Question
Improve Holiday Planner by using named ranges in an array?
Replies
1
Views
584
mrbenn
M
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
3K
pawcoyote
P
J
  • Solved
Macro Works for Me but Not Others
Replies
3
Views
2K
Fluff
Fluff
gtd526
  • Locked
  • Question Question
vba: Horizontal List to Vertical with condition
Replies
2
Views
527
Fluff
Fluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top