Lookup / Return Cell (contents)


I need a formula to look at @column1/cell (whatever it is); go into another sheet, find that match/@column1/cell, and then return what is in another column (in the 2nd sheet).

Hopefully, that isn't confusing............


  • Susan Swisher
    Susan Swisher ✭✭✭✭✭✭
    edited 07/07/20

    this is my current formula... =VLOOKUP(Submitter13, {Organization (TOTAL Spend) Range 4}, 1, true)

    it is returning "NO MATCH"

    I'm afraid I am missing something; that says, find what is in Column 2 (not Column 1); Am I missing a nested formula?


    I might have figured it out.. I had to make sure that the column, in the REF sheet, was a range? when I did that; it returned what I was expecting?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Susan Swisher

    For a VLOOKUP, the first column in the {range} is where the matching criteria is located (so the Submitter in the other sheet).

    Then in that {range}, you would have selected the columns from that matching one, all the way over to wherever the column is where you need the information from. In your formula, you specify which column to pull data from as a number after the range:

    =VLOOKUP(Submitter13, {Organization (TOTAL Spend) Range 4}, 1, true)

    Here you've put the number 1, as your second column. Instead, it should be how many columns in the range is the second column located, where you're pulling the data from that you want to be returned... does that make sense?

    ex. If my range is looking/matching a name and wanting to pull a number, and I had 3 columns in my other sheet range (NAME / DATE / NUMBER), then I would use 3 as the number in my VLOOKUP since the number column is the third one:

    =VLOOKUP(Submitter13, {Organization (TOTAL Spend) Range 4}, 3, true)

    You can read more information on VLOOKUPs in our Help Center (here). Let me know if this makes sense! If not, it would be helpful to see a screen capture of the pop-up window open, showing the Range you've selected.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!