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

teb · 04/15/22

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. · 04/19/22

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 · 04/16/22

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

Intern98 · 04/18/22

=vlookup( invoice[email protected], *reference other sheet* select table of choice, column number, true)

teb · 04/19/22

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

Genevieve P. · 04/19/22

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 · 04/19/22

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

Genevieve P. · 04/19/22

Hi@teb

This can bring back multiple values fromonecolumn 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 1to Return multiple data}, {Column with Matching Values}, [Matching Value]@row), ", ") +JOIN(COLLECT({Column 2to 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 · 04/19/22

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

Genevieve P. · 04/19/22

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.

然后确保当你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 thefirstcolumn you listed for those rows (row 3, row 25, row 102) ignoring all the other rows. Does that make sense?

teb · 04/19/22

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. · 04/19/22

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 · 04/19/22

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

teb · 04/20/22

@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. · 04/20/22

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 · 04/20/22

Smartsheet Formula Needs.pdf

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

Genevieve P. · 04/21/22

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):

Screen Shot 2022-04-21 at 1.47.23 PM.png

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"), ", ")

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

Best Answer

Answers

Help Article Resources

Categories