Importing from Excel to Smartsheet (Grid) using names for contacts

Kevin Smith

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.

Kevin Smith

Look up Table

VLookUp Grid

Please let me know if these come through.

Also attaching as files.

Lookup Tbl.jpg

Vlookup.jpg

Genevieve P.

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?

Kevin Smith

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))

Genevieve P.

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.

Kevin Smith

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!

Genevieve P.

Haha no problem! I'm definitely not the expert but I'm happy to try and help figure out what's going on!

Kevin Smith

Look up Table

VLookUp Grid

Please let me know if these come through.

Also attaching as files.

Lookup Tbl.jpg

Vlookup.jpg

Genevieve P.

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?

Kevin Smith

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.

Genevieve P.

I'm so glad that worked for you! 🙂

Kevin Smith

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

Genevieve P.

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