Trying to find the correct formula

Susan Hildebrant

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

Jeff Hoelzel

Assuming you only need a formula underneath New Agenda Submissions on down you could create 3 columns. On the sheet where you want your formula under New Agenda Submissions, add 1 filter column and 1 sequence column anywhere. You can lock and hide these after setup. In the filter column you can hard code a Y or N or use an IF formula. In the sequence column you can start and 1 then 2 then 3 and so on. Create a filter to hide rows with an "N" in the filter column. You should probably hard code a "Y" from Monthly Agenda Items down to everything through New Agenda Submissions. After that, use an IF formula so a "Y" shows up when Pending-PPRB is the vLookup result. You will unfortunately get everything and you will have to keep copying down the vLookup and logic formulas, but if you use the filter correctly, you should only see the filtered data and not a bunch of blank rows at the bottom of your sheet.

On the source sheet insert a sequence column so you can lookup the matching unique numbers and return the correct column. You can lock and hide this column if you want after you set it up.

Another way would be to link the cells and use a filter.

Jeff Hoelzel

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

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

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

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!

Jeff Hoelzel

Assuming you only need a formula underneath New Agenda Submissions on down you could create 3 columns. On the sheet where you want your formula under New Agenda Submissions, add 1 filter column and 1 sequence column anywhere. You can lock and hide these after setup. In the filter column you can hard code a Y or N or use an IF formula. In the sequence column you can start and 1 then 2 then 3 and so on. Create a filter to hide rows with an "N" in the filter column. You should probably hard code a "Y" from Monthly Agenda Items down to everything through New Agenda Submissions. After that, use an IF formula so a "Y" shows up when Pending-PPRB is the vLookup result. You will unfortunately get everything and you will have to keep copying down the vLookup and logic formulas, but if you use the filter correctly, you should only see the filtered data and not a bunch of blank rows at the bottom of your sheet.

On the source sheet insert a sequence column so you can lookup the matching unique numbers and return the correct column. You can lock and hide this column if you want after you set it up.

Another way would be to link the cells and use a filter.

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.