IF AND/OR plus a VLOOKUP

Patti Hammonds

I'm having trouble getting a formula correct; I keep getting unparsable or circular reference. I'm wanting to add the name of our rep based on the status of two columns. If the CRM Portfolio is checked AND the Jurisdiction = Federal, I want to insert the name "Jason". If the CRM Portfolio is checked and the Jurisdiction is something other than federal, I want it do a VLookup in another sheet based on the State to pull the CRM contact for that state.

I've tried - =IF(AND([CRM Portfolio]@row=1,[Jurisdiction]@row="Federal","Jason",(IF(AND([CRM Portfolio]@row=1,[Jurisdiction]@row=0,(VLOOKUP([State]@row,{CL_States Range 2},2,false))))))

This comes back as a circular error. Any suggestions?

Paul Newcome

Give this a try:

=IF([CRM Portfolio]@row = 1, IF(Jurisdiction@row = "Federal", "Jason", VLOOKUP(State@row, {CL_States Range 2}, 2, false)))

Paul Newcome

Give this a try:

=IF([CRM Portfolio]@row = 1, IF(Jurisdiction@row = "Federal", "Jason", VLOOKUP(State@row, {CL_States Range 2}, 2, false)))

Patti Hammonds

@Paul Newcome That did it! THANK YOU!! I was making it more complicated than it needed to be :)

Paul Newcome

Happy to help. 👍️