Trying to find the correct formula

I am trying to create a formula that will find the correct value in a column in one sheet and paste the value of another column on the same sheet and "paste" on a second sheet. I tried many formulas and can't find the right one. I created a VLOOKUP() formula, but it only returns the first incidence. I need it to find all the instances.

I am attaching replicas of my sheets. Here are the details:

I need to find "Pending-PPRB" in the Lookup Combo column Project Intake sheet, then copy the Project Name to the Project column on the Pending Project Requests sheet. The VLOOKUP() works. but how do I get ALL the "Pending-PPRB" to post in the Pending Project Requests sheet? I tried dragging the formula down to autofill, but I keep getting the same first result. What am I doing wrong?

Project Intake

Pending Project Requests

Best Answer


  • Jeff Hoelzel
    Jeff Hoelzel ✭✭
    edited 08/12/20

    Would a report with a filter work, or do you really need a formula?

    If you want to use a sheet and formula instead of a report, you could insert a lookup column on both sheets and sequence them i.e. 1, 2, 3, 4, ETC. vLookup the numbers and return the project name, then set a filter on your landing sheet to stack your rows.

  • Susan Hildebrant
    Susan Hildebrant ✭✭✭✭

    Great idea, but a report wouldn't work. The sheet named Pending Project Requests is a test sheet. The actual result would go to different sheet that is populated with other data.

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Susan Hildebrant

    If you really want to use a formula to get all the instance, you should then use a JOIN/COLLECT function. But in this case you'll have them all on one row which can be disturbing.

    If a report as Jeff suggested is not helping you, then I would suggest you to use some workflow to automatically copy the lines with PPRB on the sheet you want and hide all the column you don't need.

  • Susan Hildebrant
    Susan Hildebrant ✭✭✭✭

    OK, I'll try the JOIN/COLLECT function. If that fails the user will have to do a manual copy/paste. It isn't the need of the world. I just like to automate as much as I can.

    Thank you, David and Jeff. The promptness of your replies is impressive and so very helpful. That goes for all of the responders!

  • Susan Hildebrant
    Susan Hildebrant ✭✭✭✭

    Thanks, Jeff. Figuring out how to create the sequence column you suggested, but first I created a quick workflow to copy the project I wanted onto another sheet, then created the sequence. I still use the VLOOKUP and have to copy it, but at least I get what I need.

    Thanks to everyone who offered suggestions. They were greatly appreciated.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!