Importing from Excel to Smartsheet (Grid) using names for contacts
I am trying to import an excel spreadsheet where I have names in a column on each row. The names are formatted as FirstName<sp>LastName, and they are being imported then the cells are being referenced in other SS grids. I would like them to come in (during the import) as SS contacts (name, icon, email shown and can be used with automation, etc). I searched this and tried a number of things and determined (not sure) that I don't think that is possible. I can do this if I have the email but it doesn't really give me the SS contact that I want. SO, I tried using FirstName<sp>Lastname , <name@email.com> as the value, that did not work, and I tried to build that after the import and reference the cell to a cell in a col. that was contact list. I also tried to use the Name pulled into the import and reference the cell from another SS grid into a col. for contact list, that did not work. I then tried to build a lookup table with one column as Name (first last) then a col next to it with the SS contact for that name. Then in a SS grid, I used the Vlookup with no luck and then tried Index/match which did something interesting. It seemed to work but was inconsistent. After some research, it appears to be working for only those users in SS that are admins. Very difficult to figure out but I need some way to work with a name in a xl ss, and get it to a SS grid where the SS contact shows. I don't think I will have email available unless I use a lookup but using email does not give me the same SS contact that I would get if I simply started typing the name and chose from the lookup. Any help would be apricated.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
Best Answers
-
Look up Table
VLookUp Grid
Please let me know if these come through.
Also attaching as files.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
-
Hi @Kevin Smith
I can see these pictures! This is great, thank you. Try using "0" at the end of your MATCH function instead of "1". This number indicates the manner in which to search, depending on whether the range is sorted ascending (1), not sorted (0), or sorted descending (-1). It looks like your range is not sorted, so we'd want to use 0.
Try this:
=INDEX({Name Lookup Range 2}, MATCH(Name1, {Name Lookup Range 1}, 0))
Did that work?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
I have attached the grid I am using as the look up table. Named: "Name Lookup"
Also attached is the grid I am using to perform the vlookup.
This is the formula I am using that works only for the Admins (Kevin, Puru, and Bryan)
=INDEX({Name Lookup Range 2}, MATCH(Name1, {Name Lookup Range 1}, 1))
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
-
Hi @Kevin Smith
Would you be able to post a screen capture of the table you're using for the INDEX(MATCH, along with a screen capture of the sheet where you're inputting the formula?
The formula itself is built correctly, so I'd like to see the columns it's referencing (Ranges 1 & 2, along with the Name column in this imported sheet). As long as the name in the Name column matches exactly the same value that's in the column Range 1 of the other sheet, it should pull in the correct contact.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Yes, thank you for taking the time, I thought I had but it doesn't seem to have worked. I'll try a different browser. I appreciate your time, I have read all your other posts on this topic and feel you are the expert!
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
-
Haha no problem! I'm definitely not the expert but I'm happy to try and help figure out what's going on!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Look up Table
VLookUp Grid
Please let me know if these come through.
Also attaching as files.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
-
Hi @Kevin Smith
I can see these pictures! This is great, thank you. Try using "0" at the end of your MATCH function instead of "1". This number indicates the manner in which to search, depending on whether the range is sorted ascending (1), not sorted (0), or sorted descending (-1). It looks like your range is not sorted, so we'd want to use 0.
Try this:
=INDEX({Name Lookup Range 2}, MATCH(Name1, {Name Lookup Range 1}, 0))
Did that work?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you so very much. I was right about you being the expert!
That worked and I don't think I realized exactly how that 1/0 worked.
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
-
I'm so glad that worked for you! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Genevieve,
I am sorry to bother you again but I am hoping you might be able to explain. I have repeated the test I shared in this chain within the grids that I need to use it as well as repeated it in a brand new test. They are both working but only showing the names and not the true SS contact. I am not sure why it is working in the test that I shared here and not in the others. Do you have any idea why it wouldn't give me the actual contact info. I have the column set up as Contacts and I have tried it with multiple checked and unchecked.
Thank you
Thank you,
Kevin
Happy to help if I can.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)
-
Hi Kevin,
I'm happy to help! The formula will return a text value if the column it's looking at is a Contact column {Name Lookup Range 2}, but the column where you've input the formula is a Text/Number column... is that a possibility? Could you double check the column type where you've put the formula?
If that's not the issue... I'm going to need to ask for more screen captures again, haha. It would be helpful to see a screen capture like the ones above, but with the formula open in the sheet so I can see it lighting up... if you wouldn't mind taking a picture of each range in the pop-up range window that would also help!
Thanks,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!