Trouble with VLOOKUP - pulling from first row, not column

Options

I'm using the VLOOKUP for the first time to pull webinar times in from another sheet for webinar registrations automatically so we don't have to add those manually before a workflow sends meetings links out to registrants. I'd also like to use it for webinar dates if I can get it to work. My issue is that even though I've linked the appropriate columns (I think), VLOOKUP is only pulling information from the first row and not the entire column - this is without a match_type or when the match_type is true. When I use false as the match_type, I get a #NO MATCH error.

I'm attaching a Word doc with screenshots of the two sheets (columns not involved in the formula have been hidden).

I'd appreciate any input/corrections y'all might have for me! Should I be looking at a different way to do this?


Answers

  • Kelly Drake
    Kelly Drake Overachievers Alumni
    Options

    So I would recommend breaking up with VLOOKUP and using INDEX with a MATCH nested into it. VLOOKUP uses ranges and can create a world of pain from cell references if anyone shifts column order.


    = INDEX([column of data you wnat to bring over], MATCH([unique ID on the sheet you're bringing stuff to]@row, [Unique ID on the sheet that contains the data you're bringing over], 0))


    It's best if you have a unique match so you can guarantee that you're pulling over the correct data to the correct row. But using this formula you only need to reference 2 columns and it will not be impacted if your source data column order changes.

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Katherine (Katie) Smith

    I agree with Kelly.


    INDEX/MATCH is better for many reasons.

    • Can change/add columns as needed without affecting what information is shown
    • Takes fewer resources
    • Uses less cross-sheet referenced cells
    • Simpler to keep track of


    Some downsides are

    • It takes more time to set up many cross-sheet references
    • Uses more cross-sheet references


    I hope that helps!

    Be safe and have a fantastic week!

    Best,

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

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!