How can I use VLOOKUP to find data from multiple sheets and combine it in another sheet?

I have an existing database of suppliers/vendors set up in a sheet, and I'd like to automatically show if they're currently working on a project and which project they're currently working on, as well as the dates they're working on the project within that sheet. I'm creating a supplier sheet per project which shows all this information, so a straightforward VLOOKUP works just fine, but a problem arises when a single supplier is working on multiple projects (i.e. I have to reference info from multiple sheets). What is the best way to go about automatically showing this information in the database sheet per supplier?

Any help/suggestions would be very much appreciated!

Thanks!

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
  • MJ0013
    MJ0013 ✭✭✭

    Hi! Yes, so I tried creating a report to summarise the data in each of the project sheets. It is useful in itself because it gives an overview of the projects, but then I can't use VLOOKUP to get information from the report to be pulled into the database of suppliers. I've managed to find a way to combine the current project names from each of the project sheets into another sheet using JOIN(COLLECT...) but I need to be able to see the combined project names on a per-supplier basis, not for the sheets as wholes. I'm sure I'm just missing something silly or unaware of a function I can use for this purpose.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for reference? It would be helpful if we could visualize both your current data structure as well as a manually entered version of what you want to automate.

  • MJ0013
    MJ0013 ✭✭✭

    I've put together an example. I'll be setting up project info sheets that list info about the suppliers working on each project. There'll be one sheet per project - see the two screenshots below as simplified examples.

    I want the information on the status of each supplier (and later the role and dates too) to be listed in our primary supplier database (see example below).

    The problem is that a supplier is able to work on multiple projects. In the example above, Supplier 1 and 2 work on both projects.

    Is there a way to automatically show the status, role, and dates applicable to each supplier as a summary of all their projects in the Suppliers sheet? I know I can do this via a report, which is great, but I'd like the information to be part of our supplier database instead of a separate sheet which has to be checked.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The challenge is when you have a supplier working on multiple projects and there are different dates/statues/etc..


    In your above screenshots, would we pull the Status for Supplier 2 from the first project or the second?

  • MJ0013
    MJ0013 ✭✭✭

    Ideally, we'd want to pull from both and combine/concatenate. I've managed to do something similar in Google Sheets, and it does work there, but I'm unsure if Smartsheet has the equivalent capability.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. You can do that with


    =JOIN(COLLECT(....................), ", ")

  • MJ0013
    MJ0013 ✭✭✭

    Thanks, Paul. I've managed to use the =JOIN(COLLECT(.........), ",") to combine info from two cells, but not for a specific supplier, just in general. I'm looking to replicate both the 'query' part in the Google Sheets screenshot as well as the concatenation if that makes sense.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would use the same logic as you have used before with the JOIN/COLLECT.


    =JOIN(COLLECT({Source Sheet Project Column}, {Source Sheet Supplier Column}, @cell = Supplier@row), ", ")

  • MJ0013
    MJ0013 ✭✭✭

    Thanks so much for your help, this solution partially solved my problem. In the end, I decided to create separate sheets and then created a report based on those sheets instead of integrating this into the database, as it was the easier solution that wouldn't require constant updating when items changed or were updated.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!