Pulling data from another sheet (vlookup?)

Dan B.

Hello, I have a question about pulling data from another sheet and I'm hoping there is an easy way to do this.

So I have 2 sheets, one is my main sheet with all my data (collected via form), and another sheet is my roster. On my main sheet, the Employee name column is a single selection drop down menu with my employee names on it. I have another sheet, the Roster, that has two columns (exactly the same as these two in the picture) that have their Name and ID.

I would like to select an Employee name from the "Employee Name" column via drop down selection (it's tied to a form) and have the Employee ID column populate with the ID number from the other sheet based on the selection made.

I'm not very familiar with vlookup, but I think it's probably where I need to start. Or perhaps there is smartsheet functionality that I'm not seeing. Any insight is appreciated!

Thank you!

Paul Newcome

I would suggest something like this:

=INDEX({Lookup Table Sheet ID Column}, MATCH([Employee Name]@row, {Lookup Table Sheet Name Column}, 0))

Paul Newcome

Try switching the cross sheet references. The first cross sheet reference is the data you want to pull and the second is the data you want to match on. Since you want to pull the ID that should be the first range, and since you are matching on the name that should be the second range.

Paul Newcome

I would suggest something like this:

=INDEX({Lookup Table Sheet ID Column}, MATCH([Employee Name]@row, {Lookup Table Sheet Name Column}, 0))

Dan B.

Paul, first off, you are a legend, never forget that.

That being said, I am getting a #NO MATCH error.

I've included some more screen shots. This is what my roster sheet looks like with column names.

This is the formula I'm using on my data collection sheet:

=INDEX({ROSTER Employee Name Column}, MATCH([Employee Name]@row, {ROSTER Employee ID Column}, 0))

When I'm referencing the Roster sheet, do you think that I am selecting the incorrect range? As it stands, I have two separate references, the ROSTER Employee Name column and the ROSTER Employee ID column.

I'm also wondering since the name is selected from a drop down menu (via a form), is it not reading it as text or perhaps a different value or something.

Let me know if you need any more information.

Paul Newcome

Try switching the cross sheet references. The first cross sheet reference is the data you want to pull and the second is the data you want to match on. Since you want to pull the ID that should be the first range, and since you are matching on the name that should be the second range.

Dan B.

Paul, you did it again! Just an absolutely amazing job well done.

Paul Newcome

Happy to help. 👍️

AMVANWYK

Hi, is there a way that one can accidentally delete a whole column's worth of data?

Genevieve P.

Hi @AMVANWYK

Yes, if you have Editor or higher permissions and you highlight a column (by clicking the column name) you can click "delete" and it will delete out the contents of that column.

If you've accidentally deleted content from your sheet, there are a few ways you can regain this. You can find historical data in the Cell History of each cell (right click then select Cell History), or if your plan has access to it, you can see changes in the Activity Log and download a Sheet Snapshot from an earlier point in time.

See: View Changes Made to Smartsheet Items and Request a sheet snapshot

Cheers,

Genevieve

bentlb3

I have a similar challenge I'm trying to overcome...

I have a master file that I have unique ID numbers in. I have a second file with a sampling of the numbers pulled from the master file (which were worked on by a working team).

I want to match the ID numbers in my master file and populate a column in the master file with "yes" if it matches a number in my second file.

Any help is greatly appreciated.

-Brad

Paul Newcome

@bentlb3 If all you need to do is populate "Yes" if the ID is found in the other sheet, you would want to use something along the lines of...

=IF(COUNTIFS({Other Sheet ID Column}, @cell = [ID Column]@row) > 0, "Yes")

bentlb3

Paul,

That worked perfectly! Thank you so much for the help!!

Brad

bentlb3

Paul,

That worked perfectly! Thank you so much for the help!!

Brad

bentlb3

Thank you Paul! Worked great!

Paul Newcome

Happy to help.👍️

Rachael S

Hi, I am having a similar issue: I have two sheets, one is where data is input by other users, the other is one that I am using as a data analysis sheet. I want to be able to automatically add a date from one sheet to the other. I have been successful using the INDEX and MATCH formula for text, but not dates. The formula I have tried are:

=MATCH([Safety Report ID #]@row, {4. SA Review Range 1}, INDEX(DATE({4. SA Review Range 4})), 0)

=MATCH([Safety Report ID #]@row, {4. SA Review Range 1}, INDEX(DATE({4. SA Review Range 4}), 0))

and

=INDEX(DATE({4. SA Review Range 4}), MATCH([Safety Report ID #]@row, {4. SA Review Range 1}, 0))

Where:

• Safety ID and 4. 4. SA Review Range 1 is the Unique Identification number of the row
• 4. SA Review Range 4 is a date column

All help greatly appreciated.

Paul Newcome

@Rachael S Are you getting an error or an unexpected output?