How do I return cell values in a specific column based on other sheet and matching reference number.

mccoy_FSI · 08/04/22

I have a group of products all with material numbers in a database sheet. I have another sheet that I use as a database change request for for these products. When a user enters the material number on the change request sheet I want other associated values to auto-populate based on the database. Other software tools this might be called a reference column. I've looked over Index functions, this seems like it's the way to go but I can't seem to figure it out.

Andrée Starå · 08/04/22

Hi @mccoy_FSI

I hope you're well and safe!

This is the structure for an INDEX/MATCH formula.

=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 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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

mccoy_FSI · 08/04/22

I can't seem to get it to work. In the below screenshot is where I'm trying to populate the data. The screenshot is my request sheet. The material number and type column exist on the other database sheet. What I want to have happen is when the user types in the material number on this request sheet, the type should auto-populate based on the type in the database sheet that has that material number. When I try the formula you gave, in a few ways, I always get an error.

Andrée Starå · 08/04/22

@mccoy_FSI

Have you created the Cross Sheet References and replaced my formula with the names of the references and columns?

Can you paste the formula you're trying to use?

mccoy_FSI · 08/04/22

I've tried it a bunch of different ways, I'm not 100% clear what what your references mean. Here is the last one I tried =INDEX({Regulatory Course Database Range 5}; MATCH([Material Number]@row); {Regulatory Course Database Range 2}; 0))

Andrée Starå · 08/04/22

@mccoy_FSI

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

mccoy_FSI · 08/05/22

I got it worked out. Thank you.

EJ13 · 03/07/24

@Andrée Starå I have a similar problem I'm working on with index/match. I was wondering if you could take a look at my most recent response on my thread. https://community.smartsheet.com/discussion/117951/have-a-serial-number-column-update-when-there-is-a-response-on-another-sheet-with-a-new-number#latest

Mako_NZ · 07/17/24

Brilliant - @Andrée Starå's first answer solved it for me too (just had to replace ; with , and check spaces) Thanks @Andrée Starå!

Andrée Starå · 07/25/24

https://community.smartsheet.com/discussion/comment/445337#Comment_445337

Hi,

I hope you're well and safe!

Excellent! I'm glad to hear it was helpful!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

How do I return cell values in a specific column based on other sheet and matching reference number.

Answers

Help Article Resources

Categories