Pulling in data from another sheet

bentlb3

I have a master file in smartsheet that was imported from an excel file. A column of data was deleted and I need to get that data back in the smartsheet file from the main excel file.

I would like to:

• look at column 1 in the smartsheet file and look for a match in column 1 of the excel file
• if there is a match, then I would like to return the contents at that row in column 2 of the excel file and populate column 2 at the row with that data

Thank you for your help!

Brad

Debbie Sawyer

=INDEX({XL file column w/data I want to show in SS column},MATCH([SS ID number]@row, {XL ID number column range],0))

This should be a }

Can you check this bit?

Kevin Smith

Have there been new rows added to either the SS or XL files? Also, is there a 1:1 or other relationship with the rows or Col 1? There could be a few ways to handle but I would likely start by taking Col 1 from SS and Col 1 and Col 2 from XL and getting them all in one sheet to see if I could get the two XL cols. to match up with the Col 1 from SS. Once I had that, it might be easy to just put the Col 2 back into the original SS.

bentlb3

Hi Kevin. Yes, there has been modification to the SS file, so that's why I'll need to do a lookup of some sort in order to pull in the right data confidently. I was thinking that I can use a formula like this (originally provided by @Debbie Sawyer): =COUNTIFS([Column1]:[Column1], ISDATE(@cell), [Column2]:[Column2], NOT(@cell = "Yes"))

This worked great to populate a SS field with "Yes". But for this, I need to:

• have SS column 1 (ID field) look at XL olumn 1 (ID field) for a match
• if it matches, then I need SS column 2 to pull in the contents of XL column 2

Thanks!

Brad

Kevin Smith

Is there any chance that it would not be 1:1 so that if I have Col1 match more than one row on the XL file?

bentlb3

No it would have to be an absolute match. The unique ID from the SS file would match only one ID row in the excel file.

Kevin Smith

If you don't mind having a "#NO MATCH" in the SS Col 2 once you do this a VLOOKUP works well.

In the SS Grid, go into Col 2 and enter this formula with your changes: =VLOOKUP([Primary Column]@row, {XL Grid Range 1}, 2, 0)

Change the Primary Column name to be the lookup value, then make your reference sheet and range match and use 0 as the matching option.

Any entry that doesn't have a match will throw the error. If you need that resolved there are ways but it might also be easy to get get back to the raw data and copy the values, filter out and delete, etc.

See if that works, if not let me know why, there might be other ways.

Paul H

@bentlb3 It sounds like you need to use an index/match combination

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Debbie Sawyer

@bentlb3

Hi Brad

I am running a workshop this morning, but if you'd like a Zoom or MS Teams chat about this issue, then I'd happily send you an invite. (anytime after 11am BST). My email address is in my community profile, feel free to reach out.

Maybe speak soon!

Kind regards

Debbie

Andrée Starå

Hi @bentlb3

I hope you're well and safe!

I'd recommend importing the Excel again and then using INDEX/MATCH to connect the rows to get the missing information.

Something like this.

=
INDEX({ColumnWithTheValueYouWantToShow}; 
MATCH(CellThatHaveTheValueToMatch@row; 
{ColumnWithTheValueToMatchAgainsTheCell}; 0))

Depending on your country/region, you'll need to exchange the comma for a period and the semi-colon for a comma.

Did that work/help?

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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Debbie Sawyer

@bentlb3

Ok, workshop is complete, I am back! So, I would recommend that both files are in Smartsheet. so, step one would be to import the excel file into a new Smartsheet. So you would end up with the Original Import ( referred to below as SS1) and New Import (referred to below as SS2)

Go into SS1 and click in the top row of data in column2 and type

=INDEX({SS2 Column2},MATCH([Column1]@row,{SS2 Column1},0))

The {} parts of the formula signify that you are using a cross reference range (i.e. pointing to a column in another sheet). This should bring the column2 data from the New import where the column 1 from the Original Import is the same entry as Column 1 in the New Import. The ",0" bit at the end signifies that the files might not be in a sort order, so this ensures that all the rows are checked before a no match error is displayed.

The Index / Match nested function described here works the same way as a VLookup also recommended in this thread. HOWEVER, Smartsheet recommends using an Index(Match()) over a Vlookup as it uses fewer cell links. There used to be a video explaining both and the benefits of one over the other, but I can't seem to put my finger on this quickly! It doesn't really make a huge difference if you are only performing a small vlookup in 1 column on a sheet, but if you need multiple columns of looked up values you will hit the cell link limit a lot quicker using Vlookups over Index(Match()) functions.

If you do not want any errors displayed, i.e. you don't want the "No Match" value returned if no match was identified, then you could surround the formula with an IFERROR clause. I use "" as my error handling, which just means display nothing instead of an error message. To get this onto your formula you can do this:

=IFERROR(INDEX({SS2 Column2},MATCH([Column1]@row,{SS2 Column1},0)),"")

You don't have to have it, I just find it is a little bit tidier.

I hope this helps.

Kind regards

Debbie

bentlb3

Thank you all for your feedback all! @Debbie Sawyer I am getting a #unparseable result with your formula. Here is what I did:

I brought in the Excel file that I want to pull data from (I'll call it the XL file) and into my file already in SS (call it SS file)

I applied your formula like this: =INDEX({XL file column w/data I want to show in SS column},MATCH([SS ID number]@row, {XL ID number column range],0))

Unfortunately I received the error. Anything I'm doing wrong?

Thanks!

Debbie Sawyer

=INDEX({XL file column w/data I want to show in SS column},MATCH([SS ID number]@row, {XL ID number column range],0))

This should be a }

Can you check this bit?

bentlb3

That's it! Thank you so much. Everything is working perfectly! I appreciate all your help and the detailed instructions for this formula!

Thank you, Brad

Debbie Sawyer

@bentlb3

You are most welcome! Glad it is working for you!