VLookup not returning a contact

John Reynolds

I am using vlookup to look up a contact from a source sheet and have it insert it as a Contact in another sheet.  It is looking up the right person, but is returning a text only of the person's name.  I wanted as email contact to in the target sheet to use for a workflow automation.  How can I get it to return a contact?  The column is set as a Contact List.  The formula is =VLOOKUP([Community of Practice]@row, {CoP & Domain Leadership Range 4}, 9, false)

Andrée Starå

Hi @John Reynolds

Double-check that the contact is formatted correctly, so the name and email are in the right fields.

Was that the issue?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Ashley Woodworth

Hi @John Reynolds , I am having the same issue. I met with an account manager at Smartsheet who saw the issue, agreed it was formatted correctly and was surprised the formula wasn't working. She advised me to trying and switch to using a combination of IF, MATCH, COLLECT and INDEX statements, but unfortunately I haven't figured out how to do this correctly yet since my goal is to look up an approval contact from another sheet based on two fields within the sheet (the first being a cost center number and the second being the total value of the line item). It is returning the correct Ashley with the VLOOKUP, but like you decribed it is just a name, no little picture showing, not properly thinking over the contact itself. This is an issue for me because I need my approval workflow to operate based on "contact in cell" which is found through this VLOOKUP.

@Andrée Starå I'm guessing John are having very similar issues if you are able to help. I am not well versed in the MATCH/ COLLECT/ INDEX functions. My current formulas (that work, and set up to be formatted correctly, but is not showing up as a contact are:

This formula is validating that the requestor is not equal to the approver referenced in another sheet, and is so directing it to an alternate approver, also in another sheet.

=IF(VLOOKUP([Cost Center/ Profit Center]@row, {PO/ FO $$ Request Look ups Range 1}, 6, false) = Requestor@row, VLOOKUP([Cost Center/ Profit Center]@row, {PO/ FO $$ Request Look ups Range 1}, 7, false), VLOOKUP([Cost Center/ Profit Center]@row, {PO/ FO $$ Request Look ups Range 1}, 6, false))

This formula is looking at the cost center, listed in a column on the same sheet, using it to reference an secondary approver on another sheet, however the cost value of the line item could indicate one of 6 different approvers)

=IF([Total Cost per PO Requisition]@row < 10000, [Approver <$10k]@row, IF([Total Cost per PO Requisition]@row < 25000, [Approver <$25k]@row, IF([Total Cost per PO Requisition]@row < 50000, [Approver <$50k]@row, IF([Total Cost per PO Requisition]@row < 100000, [Approver <$100k]@row, IF([Total Cost per PO Requisition]@row < 250000, [Approver <$250k]@row, IF([Total Cost per PO Requisition]@row > 250000, [Approver >$250k]@row))))))

john.misiewicz@gmail.com

Having the same issue even when the formula return column is formatted with the allowed contacts in the column formatting. The match/index/collect refuses to work.

Paul Newcome

You shouldn't have to use the MATCH function when using an INDEX/COLLECT combination.

=INDEX(COLLECT({Range to pull from}, {1st Criteria Range}, 1st_criteria, {2nd Criteria Range}, 2nd_criteria), 1)

Grace Qin

https://community.smartsheet.com/discussion/74054/vlookup-not-returning-a-contact

Just an idea: In Column Properties, make it Contact List and allow multiple contacts per cell.

I am using IF, and the column is Contact List, but it still only show the name as plain text until I turned on the allow multiple contacts per cell.