# Return Multiple Rows Using VLOOKUP and COLLECT

Options

Good morning,

I am working on a formula which will return multiple rows of data from a different sheet based on criteria.

For context, I am looking to view all the cells from sheet A (defined in a range) where there is a value like "BA Edit" in sheet B. I have tried doing this several ways, with no success.

=if(CONTAINS({BA Edit 2}, "BA Edit"), {BA Edit 2}@row) is #unparseable

=IF(CONTAINS("BA Edit", {BA Edit 2}), {BA Edit 2}, "False") is #invalid column value

=VLOOKUP(CONTAINS([Primary Column]@row, {BA Edit 2}), 1, 0)) is #unparseable (note [Primary Column]@row has the value of "BA Edit"

=COUNTIF({BA Edit 2}, =(CONTAINS("BA Edits", {BA Edit 2}))) returns a value of 0, despite there being approximately 40 in the range.

Any help is appreciated!!

Thank you all,

• ✭✭✭✭✭✭
Options

I'm not sure I follow exactly what you are trying to do. Are you able to provide some screenshots with mock data manually entered that shows your desired end result?

• Options

Thank you Paul, for getting back to me. I am not sure I can provide a screen capture that would help, but let me try and better explain the context.

I have two sheets:

• project plan sheet (sheet A)
• data mining sheet (sheet B)

In sheet B, I would like to display all the tasks from sheet A that are related to a certain job function (BA Edits, for example).

Does that make more sense?

• Options

Here is a mock-up

• ✭✭✭✭✭✭
Options

Have you looked into a report?

• Options

The report is the endgame. First I need to take the scrubbed data and perform calculations on each row, then I would further filter into a report.

• ✭✭✭✭✭✭
Options

What types of calculations are you trying to run? Could you run those calculations on the main sheet? Are you able to give a broader explanation of what you are working on? There may be a different solution other than attempting to use formulas just to pull base data from multiple rows into a separate sheet.

• Options

Thanks for sticking with me Paul!

I didnt describe what I was doing very well. For this particular use case, I am building a sheet which displays the predecessor/successor relationship between tasks.

As an example, what I am attempting to do:

• is create a sheet that displays only the tasks that contain the text "BA Initial Draft" (in column A below) and brings with it associated data from the row (Columns B-E, which I am currently doing with a formula based on Column A).
• Then I would want to automatically populate the successor task which will always contain the text "Requirements Review" on the same row based on the data in column A.

This way I no longer have to manually adjust the sheet when new "BA Initial Draft" data is entered.

I hope this makes some sense 🤣

• ✭✭✭✭✭✭
Options

Ok. But why are you pulling it to a separate sheet in the first place?

• Options

I guess I could put it all in the master sheet, but I feel like I would have the same issue with formulas in my auto population of the successor task in a different column.

• ✭✭✭✭✭✭
Options

As long as you have a column that contains some unique data for each row, you should be able to adjust your formulas. Are you able to mock up a sheet that would show the end result and provide your formulas that you are currently using to pull the data in the different columns?

• Options

I was working on this on and off all day. If I try pulling the information into the same sheet (new column), my vlookup isnt working.

=VLOOKUP(Predecessors@row, [Row ID]:[Row ID], 1, 1)

• Options

Paul, thank you for your help, but I am going to work on a different way of working my problem. I will post here when I figure something out.

• ✭✭✭✭✭✭
Options

Ok. Sounds good.

• edited 01/21/21
Options

For what its worth, I sent this over to smartsheet customersuccess and they're still trying to figure it out :)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!