Auto Add Contact with IF Formula

tplaisance

Hello!

I am trying to get a contact to auto populate IF a column equals a certain value. For Example:

So if the GC/Owner = Gallo Mechanical, LLC i want the "Submit to" = the AP contact.

Can you help me to create a formula? Every version i have tried has not worked.

Thank you!

PCG Sam Harwart

Hi @tplaisance ,

Where are the AP contacts stored? Are they in a different column or on another sheet? Or do you want the options to be contained within the formula?

tplaisance

@PCG Sam Harwart

I have them in a separate sheet that contains all my contacts.

Mark.poole

@tplaisance

You have two options. You can do a =Vlookup(), or what I believe to be a better option is Index Match. EX.

=Index({Email Reference},Match([GC/Owner]@row,{GC/Owner Reference},0)))

=Vlookup([GC/Owner]@row,{Reference},2,0)

tplaisance

@Mark.poole

Thank you! I tried both and they still did not work. I used the Index Match first, and added the link to the Contacts page. I am still getting an error of "#UNPARSEABLE"

Mark.poole

@tplaisance

Are your references Single Column? One for Each the email and GC/Owner?

tplaisance

@Mark.poole

Yes. I have selected the column and attached the reference to the Contact Sheet. This is what i have:

=Index({{SMS Contacts Range 3}},Match([GC/Owner]@row,{Column 5},0)))

Mark.poole

@tplaisance

as your building the formula you will see it say "Reference another sheet" Click that to build your references.

For the First reference select the column header for the email on your reference sheet. Lets name this one Email.

For your second reference pick the column in reference sheet that has the GC/Owner. We can call this one GC/Owner.

=Index({Emai},Match([GC/Owner]@row,{GC/Owner},0)))

tplaisance

@Mark.poole

Thank you for that Mark. The issue i am running into now is that there is no reference sheet for GC/Owner. It is on the sheet i am currently trying to input the formula in. Is this a problem?

Mark.poole

@tplaisance

Yes that would be an Issue. GC/Owner should be on the same sheet as the email reference sheet. So any future sheets or rows you create know what email it should match your GC/Owner to. Be sure you put the correct GC/Owner on the same row its email in the reference sheet.

tplaisance

@Mark.poole

I added an additional column to my reference sheet. This is what it looks like.

This is my current forumla:

=INDEX({{SMS Contacts Range 3},Match([{SMS Contacts Range 1}@row, {{SMS Contacts Range 1}},0)))

Range 3 is the email address column, and range 1 is the business column

Mark.poole

@tplaisance

Ok. So on the sheet you're trying to pull the information to You should have the formula

=Index({SMS Contacts Range 3},Match([GC/Owner]@row,{SMS Contacts Range 1},0)))

tplaisance

@Mark.poole

I am still receiving the error.

Mark.poole

@tplaisance I had 1 to many ) at the end

=INDEX({SMS Contacts Range 3}, MATCH([GC/Owner]@row, {SMS Contacts Range 1}, 0))

This is using the formula you should use. with a reference sheet matching information you gave in yours.

tplaisance

@Mark.poole

THANK YOU

Mark.poole

@tplaisance

You are very welcome