Formula to cross reference data and compile into one sheet
Hello,
I am needing to take data from one sheet and create a simplified version of it in another sheet. I have tried using MATCH & INDEX and VLookup formulas but none have seemed to work. Details on what I am specifically trying to do are below.
Sheet number 1 has raw data in it extracted from a survey, this raw data is connected to a specific user ID wihtin the same row. Sheet number 2 will take this raw data and reference certain cells all while using the specific user ID.
For example: sheet number one says customer003 has passed the evaluation. Sheet number 2 shows a row for customer003 and under column "evaluation" it reference sheet 1 to show the evaluation has been completed.
I was utilizing MATCH and INDEX since VLookup only references the first column but MATCH and INDEX do not seem to be working when I attempt to do this.
Does anyone know why? Thanks!
Answers
-
An INDEX(MATCH formula sounds like it would be best for this, you're right! When you say that it's "not working", could you be more specific?
An INDEX(MATCH works like this:
=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}), 1)
Or in your case:
=INDEX({Column with Complete}, MATCH([User ID]@row, {User ID in the other sheet}), 1)
If you have multiple criteria to look for (for example the User ID and other elements), then you may want to use an INDEX(COLLECT. Paul has a great example of this in his response on this post, here.
If this isn't working for you, it would be helpful to see a screen capture of both your source sheet and destination sheet, along with any errors you are seeing and a copy/paste of the formula you're using... but please block out any sensitive data!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Your solution is for Excel and not Smartsheet so that it won't work, unfortunately.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!