Index Match Between 2 Sheets

Kathy Benner

I am trying to use Index Match between 2 sheets to pull a value and can't figure out how to get it to look at the other sheet as a reference. I have tried several variations from what I have found here but can't get bast error messages. I want to pull the name from column "eCW" in the sheet called "eCW Users" into column "eCW" on the sheet called "Employee Directory". I feel like it should be fairly simple. Display Name is the first column on both sheets and the cell I am trying to match to pull the info over but I can't find a solution that I can get to work.

Any help I could get would be GREATLY appreciated. My goal is to keep a master that I can pull reports but split the info for sheet sharing with specific users.

Thanks - Kathy

Kelly Moore

=INDEX({ecW Users eCW}, MATCH([Display Name]@row, {ecW Users Display Name},0))

As always, you'll have to build the cross sheet references instead of copy paste from here. This is what it will look like. Smartsheet will insert generic range names like {ecW Users Range 1) instead of how I named them. You can change this generic name if you choose (it's a good practice to do so) so you will know exactly what you are referencing).

Kelly Moore

Hey @Kathy Benner

I'd be happy to help. To give you the specific formula, in addition to the info you provided above, I'm unclear on what is the reference between the two sheets.

Index/Match uses the following syntax

=INDEX({The information you want to gather from the source sheet- in your case eCW Users Name column}, MATCH([the reference on the sheet where the formula is-in your case Employee Directory column?]@row, {the reference to match on the source sheet- eCW User column?},0))

The zero tells the Match function that your data is unsorted. You need that.

In your Employee Directory sheet do you have something like a Employee ID column that we could use to reference a matching Employee ID column in the eCW User sheet? That would work.

Kelly

Andrée Starå

Hi @Kathy Benner

I hope you're well and safe!

This might help.

=INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row;

{ColumnWithTheValueToMatchAgainsTheCell}; 0))

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

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

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!

Kathy Benner

I am using a common "Display Name" column in each sheet as the match criteria

Kelly Moore

Hey Kathy

Perhaps I misunderstood, I thought the Display Name was the information you are trying to bring over from the other sheet. This is the INDEX portion of index/match. The match portion says in this big list of data (names in your case), you want this name based on what it is referenced to (like address, date, employee id) in that row. Match defines what specific item is collected from the list for the particular row you are sitting on. Think of a telephone book. You Index the phone number by Matching the name

A report (vs a sheet) will collect the entire list of names from the sheet and display whatever other columns of data you wish to display. Would a report work for you?

If you are able to provide screenshots of both of your sheets (remove sensitive info), then the community will be able to help you more quickly.

cheers,

Kelly

Kathy Benner

So my 2 sheets are eCW Users and Employee Directory. My common column on both is Display Name. I am trying to pull column "eCW" from my eCW Users sheet TO column "eCW" in my Employee Directory sheet. So if I follow correctly then it would be

=INDEX({eCW Display Name}, MATCH(Employee Directory Display Name]@row, eCW User eCW},0))

Kelly Moore

If your {eCW User eCW} is the Display name column then yes! perfect! Since you're building cross sheet references, don't forget you have to INSERT the REFERENCE vs just typing the formula in your formula window.

Nice job!

Kelly

Kathy Benner

I have tried all combinations and continue to get #UNPARSEABLE. I am not understanding where to reference the other Sheet name that I want to pull the information from... in this case eCW is where my source information is housed and I want to pull column eCW into Employee Directory column eCW.

I'm sorry that I'm just not able to wrap my head around referencing the second sheet.

K

Kelly Moore

Hey Kathy

Good question.

When you are building a cross sheet reference, you must use the Insert Reference link to find the sheet and select the column (range) on that sheet.

In the sheet where the formula resides, type =INDEX( then select the Insert Reference

A dialogue box opens where you can find the sheet, select the column you need, Rename the range to reflect the column name, then click Insert Reference. The reference will insert in your formula.

Move to the Match function and do the same when you get to the 2nd cross sheet reference.

Double check your commas, curly brackets and parentheses that they are all in the correct spot. The formula wizard will help (see the highlighted yellow text in screenshot above. Move your cursor to different parts of your formula)

Does that solve the unparseable? If not, give me a screenshot of the formula.

Kelly

This video may help with cross sheet formulas

Kathy Benner

Here are the blanks I am trying to fill in picture form...

A picture is worth a thousand words, I can't get a result beside #UNPARSEABLE still

Thank you for your patience - k

Kelly Moore

=INDEX({ecW Users eCW}, MATCH([Display Name]@row, {ecW Users Display Name},0))

As always, you'll have to build the cross sheet references instead of copy paste from here. This is what it will look like. Smartsheet will insert generic range names like {ecW Users Range 1) instead of how I named them. You can change this generic name if you choose (it's a good practice to do so) so you will know exactly what you are referencing).

Kathy Benner

Ok, thank you so much! I finally got it to work with the above, I struggled with the MATCH references and ended up having to build those out separately for some reason but then was able to piece them together. I have cell links between a lot of sheets and Display Name is the great connector so this will come in very handy moving forward.

Is there a way to have this dynamically add to the next row when I add a new employee or is it just a manual copy down? Just wondering if I could save a few steps.

Thank you again, I really appreciate your patience

k

Kelly Moore

Hey

Glad you got it working. As you use the Display Name in other sheets, another handy set of functions is INDEX/Collect. It works similar to Index/Match however Collect will allow you to filter the 'match' on multiple criteria- unlike MATCH which traditionally only allows one criteria to match on.

In terms of keeping the Display Name dynamically synced - that is a frequently requested enhancement here in the community. If you have access to the Premier App Data Mesh, it's sort of possible - it can collect updated and new info via a report which can be used as a Data Mesh source but Data Mesh doesn't seem to delete Deleted data (at least what I've found. grrrrrrr!). You can google 'smartsheet copy specific column without copying entire row' and see instructions for 3sheet work around. And finally, if the Display name data can automatically be dumped into excel AND if you have the Premier app Data Uploader - you're golden- you can automatically update your Display Name column(s) using data uploader. Others in the community may have additional/different advice on dynamic ranges.

cheers

Kelly

Kathy Benner

I am definitely going to look into the INDEX\Collect since I just set up the INDEX\MATCH for 3 seperate columns using the Employee Directory as a key and I'm getting the Ranges all messed up with names. I researched a little and it doesn't look like I can change or delete a Range once it has been defined.

Thank you again,

k

Kelly Moore

Hey

Are you talking about changing or deleting a cross sheet reference range? They can be changed and deleted - however any change affects any formula already using that particular reference. Sometimes that is overlooked when one uses the Edit Reference link in the formula window. Shout out if you run into a snag on that.

darrell.stevens

Morning all.

I'm working on a first time INDEX/MATCH formula. I worked pretty fervently on this and found it to be a bit of a vendeta to solve it. But alas! Nada!

I'm obviously missing a key element in my formula. Looks so easy online right?

1. 2 sheets = "Template" = destination, "T-minus launch dates" = source
2. Same column in both = [Weeks]
3. I have tried the "reference another sheet" in the formula help. Now that said, if my range is just 1 cell for this test would it be [Weeks]@row? In this image I left the range as what SS would set it as.
4. I've tried several variations of the suggested formulas for other users but I get #unparseable
5. Here's my formula as of now.
6. =INDEX({Template Weeks}, MATCH([Weeks]@row, {T-Minus Launch Dates},0))