Multi-Conditional IF Statement with Too Many Conditions. Alternatives?

[madelynnharrah]

So, Excel reads my formula. It tells me it's correct, but it has too many statements. [I know this is true, because the equation is absolutely huge. )
The thing is, I need it to tell me, based on two separate columns - one being "State" and the other being "A vs. O", who this contact belongs to. [For each state, there is a different person for A and for O, and the entire spreadsheet is text.]

My ridiculously large, almost functional function is:
=IF(AND(RC[9]="WA",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="WA",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="MT",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="MT",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="ID",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="ID",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="OR",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="OR",RC[13]="OPP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="APP"),"Tom Wieske"),IF(AND(RC[9]="CA",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="NV",RC[13]="APP"),"Tom Wieske"),IF(AND(RC[9]="NV",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="AZ",RC[13]="APP"),"Tony Wieske"),IF(AND(RC[9]="AS",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="WY",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="WY",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="UT",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="UT",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="CO",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="CO",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="NM",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="NM",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="TX",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="TX",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="OK",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="OK",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="AR",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="AR",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="LA",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="LA",RC[13]="OPP"),”Tony Stephenson"),IF(AND(RC[9]="MS",RC[13]="APP"),"Kyle Johnson"),IF(AND(RC[9]="MS",RC[13]="OPP"),"Tony Stephenson"),IF(AND(RC[9]="ND",RC[13]="APP"),"Tom Wieske"),IF(RC[9]="ND"),"Tom Wieske”),IF(RC[9]="SD"),"Tom Wieske”),IF(RC[9]="NE"),"Tom Wieske”),IF(RC[9]="MN"),"Tom Wieske”),IF(RC[9]="WI"),"Tom Wieske”),IF(RC[9]="IA"),"Tom Wieske”),IF(RC[9]="KS"),"Raylene Hayden”),IF(RC[9]="MO"),"Raylene Hayden”),IF(RC[9]="IL"),"Raylene Hayden”),IF(RC[9]="IN"),"Raylene Hayden”),IF(RC[9]="MI"),"Raylene Hayden”),IF(RC[9]="TN"),"Matt Winters”),IF(RC[9]="AL"),"Matt Winters”),IF(RC[9]="GA"),"Matt Winters”),IF(RC[9]="FL"),"Matt Winters”),IF(RC[9]="SC"),"Matt Winters”),IF(RC[9]="NC"),"Matt Winters”),IF(AND(RC[9]="MD",RC[13]="APP"),"Alan Peisakoff"),IF(AND(RC[9]="MD",RC[13]="OPP"),"Elizabeth Grow"),IF(AND(RC[9]="NJ",RC[13]="APP"),"Alan Peisakoff"),IF(AND(RC[9]="NJ",RC[13]="OPP"),"Elizabeth Grow"),IF(AND(RC[9]="DE",RC[13]="APP"),"Alan Peisakoff"),IF(AND(RC[9]="DE",RC[13]="OPP"),"Elizabeth Grow"),IF(AND(RC[9]="CT",RC[13]="APP"),"Alan Peisakoff"),IF(AND(RC[9]="CT",RC[13]="OPP"),"Elizabeth Grow"),IF(AND(RC[9]="RI",RC[13]="APP"),"Alan Peisakoff"),IF(AND(RC[9]="RI",RC[13]="OPP"),"Elizabeth Grow"),IF(AND(RC[9]="MA",RC[13]="APP"),"Alan Peisakoff"),IF(AND(RC[9]="MA",RC[13]="OPP"),"Elizabeth Grow"),IF(AND(RC[9]="VT",RC[13]="APP"),"Alan Peisakoff"),IF(AND(RC[9]="VT”,RC[13]="OPP"),"Elizabeth Grow"),IF(AND(RC[9]="NH”,RC[13]="APP"),"Alan Peisakoff"),IF(AND(RC[9]="NH”,RC[13]="OPP"),"Elizabeth Grow"),IF(AND(RC[9]="ME”,RC[13]="APP"),"Alan Peisakoff"),IF(AND(RC[9]="ME”,RC[13]="OPP"),"Elizabeth Grow"),IF(RC[9]="NY”),Kevin Krethcun),IF(RC[9]="PA”),Kevin Krethcun),IF(RC[9]="OH”),Kevin Krethcun),IF(RC[9]="WV”),Kevin Krethcun),IF(RC[9]="VA”),Kevin Krethcun),IF(RC[9]="KY”),Kevin Krethcun), FALSE"))


As you can clearly see, there are a lot of terms, but I need both the "State" and the "A vs. O" column to determine the outcome of the column the formula will be in. And I hope this makes enough sense for someone to come to my rescue.

Thanks so much!


-M.

 

[bschwartz]

If you have all of the data laid out on the Excel table (state, A vs. O, contact, etc), you can use a VLOOKUP or INDEX/MATCH.

Can you provide more information about your data?

 

[madelynnharrah]

To an extent, yes. Confidentiality agreements make this a bit difficult.

So, column A is where I need my formula to be. It needs to give the name of a specific sales representative.

The spreadsheet is contact data, and based on what state [Beginning at J4] the contact is in as well as whether it is "O" or "A" [Beginning at N4], that contact is sent to a specific representative.

 

[madelynnharrah]

I've been writing this Match/Index, but to no avail. My table is on a separate page of the workbook. I need it to take the first two columns of that table, compare J4 and N4 determine which name in column three the contact goes to.


The formula I just tried:

=INDEX('Rep to State Cross Reference'!R[-2]C[-1]:R[93]C[1], MATCH(ContactList!RC[9]&ContactList!RC[13],0))

[Rep to State Cross Reference and Contact List are the two pages of the workbook.]