Can VLOOKUP return multiple row results?

Dolly L.

Hello -

A bit stumped and would appreciate some advice. I am trying to lookup values in Sheet 1 and populate the ID into Sheet 2.

In Sheet 1, I have Intake Status and ID columns as shown:

I am trying to pull the ID into Sheet 2 for any instance in which the Intake Status is New. I used a VLOOKUP formula as follows:

=VLOOKUP("New", {Status and ID}, 2, false)

However, that only returns the first value of TMO-0315 and does not pick up any other IDs with a New status. When applying as a column formula I end up with multiple rows in Sheet 2 that pull the ID TMO-0315.

How do I get the formula to return all IDs that show an Intake Status "New"?

Thank you!

Jake Gustafson

Following as I'm interested in whether there is a solution for this.

Sameer Karkhanis

Is your requirement to have the data in Sheet2 as,

Project - Hold/Defer : TMO-0309

Project : TMO-0310, TMO-0312, TMO-0313

Archive : TMO-0311

New : TMO-0315, TMO-0316

Dolly L.

Hi Sameer,

My goal is to have the formula identify any item with an Intake Status of New and copy over the ID to Sheet 2. Currently I have four items in Sheet 1 that match that requirement (image only shows two). My challenge is that only the first valid row is picked up by the formula (row 40 with ID TMO-0315) and I can't get the formula to read down the list and identify additional valid items.

Jake Gustafson

Could you use the Copy Automation Workflow to move the Intake Status (New) rows over, then have additional columns with VLOOKUPs or INDEX/MATCH formulas to populate things based on the IDs that are there?

Dolly L.

Hi Jake,

Thanks for the suggestion. I did consider that, however the rows in Sheet 1 have a ton of columns that I do not need to duplicate in Sheet 2. I do use a few of the columns in Sheet 2 and have a Data Mesh set up for the relevant columns.

I just need a way to only copy over the IDs for the New projects into Sheet 2.

In addition to VLOOKUP, I did try a cross-sheet INDEX/MATCH option which gave the same result of only returning the first item. I tried IF with cross=sheet references and that did not work at all.

Open to any other suggestions.

Thank you!

Jason Duryea

Are you looking to have the ID's found to be listed on separate rows or just trying to gather all of the ID's into one cell in a comma delimited format?

Travis Ryan

I am struggling with this same need. Pull the data needed from a column on a separate sheet and populate it in another sheet on separate rows so they can be utilized in additional column formulas.

Dolly L.

@Jason Duryea Yes, I am trying to have the IDs found on Sheet 1 copy into Sheet 2 on separate rows. Idea is that on an on going basis, as New project requests are received in Sheet 1, the ID will be copied over into Sheet 2 on separate rows. Hope that makes sense.

Andrée Starå

Hi @Dolly L. & @Travis Ryan & @Jake Gustafson

I hope you're well and safe!

To add to previous excellent advice/answers.

Here's a possible workaround or workarounds

• You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.
• Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.
• You could add a so-called helper column (Automatic or Manual) in the Source Sheet with a running number 1,2,3, and so on and then add it to the Destination Sheet as well and use that for the INDEX/MATCH so you'd always get the information you need.

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Dolly L.

@Andrée Starå Thanks for the suggestions. The ID info that I am trying to copy over is an auto generated project ID. I've tried cross-sheet formulas combined with VLOOKUP and with INDEX/MATCH. In both cases, the end result is that the formula only reads the first occurrence of a New project and only copies over that number. The challenge I am having is getting the formula to keep reading down the Intake Status column and identifying all other New items. Open to any pointers on that latter piece....

And thank you :).

Floretti

I'm interested in this as well. I've been doing lots of research and it seems Smartsheet can't return multiple values in multiple rows without some workaround that ends up not worth the effort to keep it running. I'm a bit disappointed to be honest as these are simple things we can achieve in Excel and Google Sheets.