How can I pull data from different sheets with vlookups/index formula in one column?
In an easy way to explain what i want to achieve:
I have 3 smartsheet files:
1- Tops ( contains all tops with price)
2 - Bottoms ( contains all bottoms with price)
3 - Master Sheet ( contains all tops and bottoms)
I want to set up a price column in the master sheet links to the tops or bottoms sheet based on the item and reflect the price. If the price is changed in the individual sheet, it will update on the master sheet as well.
So far I explored with vlookups and index function, was able to pull the data back from one sheet. Not sure how can I update the formula to pull data from different sheets based on the items? Let's say i have a column called " category" that indicates top or bottom, so I know which sheet to pull.
Answers
-
You can wrap your lookups in an IF statement to manage which source sheet to pull from. I threw in a "No Pricing" to call out errors on your Tops and Bottoms sheet - you can do a troubleshoot of what's need by constructing a report of only those items saying "No Pricing".
=IF(Category@row="Top", INDEX/MATCH FORMULA TO YOUR TOPS SHEET, IF(Category@row = "Bottoms", INDEX/MATCH FORMULA TO YOUR BOTTOMS SHEET, "No Pricing")
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
Thank you so much! Let me give it a try!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!