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

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?

Best Answer

«1

Answers

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

  • Intern98
    Intern98 ✭✭✭

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

  • teb
    teb ✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    teb ✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    teb ✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin
    Answer ✓

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

    Cheers,

    Genevieve

  • teb
    teb ✭✭

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

  • teb
    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.
    Genevieve P. Employee Admin

    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
    teb ✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!