How can I pull data from different sheets with vlookups/index formula in one column?

Options

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.

Tags:

Answers

  • Kerry St. Thomas
    Kerry St. Thomas ✭✭✭✭✭
    Options

    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!

  • zdy520kobe
    Options

    Thank you so much! Let me give it a try!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!