I want to use vlookup to pull data from 1 sheet to another, but it isn't working, & I don't know why

teb

Sheet one houses invoices. Sheet two houses statements. I want to be able to pull invoice numbers from sheet in three different rows into one statement row in sheet two. So, one cell in a row in sheet two under a column titled "Invoice Number" would contain 3 or 4 invoice numbers pulled from sheet one. My formula always comes up unparseable. Why?

Genevieve P.

No problem! Here are some videos and resources that may be more helpful, as they'll have visuals to go along with creating formulas:

• Article: Create and Edit Formulas in Smartsheet
• Article: Formulas: Reference Data from Other Sheets
• Webinar: Formulas webinar series
• Webinar: Smartsheet Formula Best Practices

Cheers,

Genevieve

Amy.Mizzi

Can you share your formula and screenshots of your 2 sheets, with any sensitive data removed?

Intern98

=vlookup( invoice number@row, *reference other sheet* select table of choice, column number, true)

teb

I don't have separate tables in my Smartsheet. So, a formula using a table will not work.

Genevieve P.

Hi @teb

It sounds like you actually want a JOIN(COLLECT formula instead of an Index(Match or Vlookup which will only bring back one matching value.

Try something like this:

=JOIN(COLLECT({Column to Return multiple data}, {Column with Matching Values}, [Matching Value]@row), ", ")

Cheers,

Genevieve

teb

Ok. Can I pull data from multiple columns and/or multiple rows using that formula?

Genevieve P.

Hi @teb

This can bring back multiple values from one column based on a matching value in a different column.

If you have multiple columns to look into, you can use + to add together formulas:

=JOIN(COLLECT({Column 1 to Return multiple data}, {Column with Matching Values}, [Matching Value]@row), ", ") + JOIN(COLLECT({Column 2 to Return multiple data}, {Column with Matching Values}, [Matching Value]@row), ", ")

Let me know if that makes sense! If this isn't working for you, it would be helpful to see screen captures of both sheets, identifying what it is you want to bring across with an example, but please block out sensitive data.

Cheers,

Genevieve

teb

@Genevieve P. Ok. Will it work with multiple rows? Thank you for helping me understand the limits of the formula.

Genevieve P.

Hi @teb

When you select the first range:

=JOIN(COLLECT({Column to Return multiple data},

this {column to return} is the entire column in your other sheet. This includes every cell down the entire sheet for this column, so multiple rows.

Then just make sure that when you select the second range:

{Column with Matching Values}

You select the entire column as well. What the formula does is it first finds the matching value in this second column, like a filter. It finds all of the matching values (eg. row 3, row 25, row 102), then it brings back the cell value from the first column you listed for those rows (row 3, row 25, row 102) ignoring all the other rows. Does that make sense?

teb

Hello @Genevieve P.

Thank you. It kind of makes sense. I am still learning. I will play with this and see what happens.

I appreciate your assistance!

Genevieve P.

No problem! Here are some videos and resources that may be more helpful, as they'll have visuals to go along with creating formulas:

• Article: Create and Edit Formulas in Smartsheet
• Article: Formulas: Reference Data from Other Sheets
• Webinar: Formulas webinar series
• Webinar: Smartsheet Formula Best Practices

Cheers,

Genevieve

teb

@Genevieve P. thank you for the resources! Awesome.

teb

@Genevieve P. Well, this formula got me closer than I have ever been. So, thank you for that. I received an error message that the rows have to be next to one another in order to be included. My rows will not be next to one another. Is there a way to accommodate that issue? Thank you for your help.

Genevieve P.

Hi @teb

Can you post the formula you're using, the exact error message you're receiving, and a screen capture of both sheets? (With sensitive data blocked out).

teb

Smartsheet Formula Needs.pdf

@Genevieve P. I hope this is helpful. I appreciate your assistance. Thank you!

Genevieve P.

Hi @teb

Thank you for this, but we're still missing some information. I see here the column you want to return, so the multiple values you want to bring back (Test 1, Test 2, etc), but how do you know what rows you're looking for?

What's the unique identifier across your sheets? What tells the formula in Sheet 2 that those three Tests are all associated with one another? Think of it like putting a filter on: what filter criteria do you apply to Sheet 1 so only those 3 rows appear?

For example, if this is your Source Sheet (Sheet 1 in your image):

Then you can see that the "Unique Value" column has 3 cells that contain the same value, "yyy".

I can use this in the JOIN(COLLET to bring back the 3 data points in the Bill Number column because they have the same criteria in the Unique Value column:

=JOIN(COLLECT({Bill Number}, {Unique Value}, "yyy"), ", ")