Same IFERROR | VLOOKUP formula works on some lines and not others

Hello,

I'm currently having issues with my VLOOKUP auto-populate formula, which is spread across multiple sheets.

(1) Formula works in another sheet;

(2) As shown below, I didn't receive an error message. However, it isn't pulling the information at all and instead inputs "-" or blank in each field.

Formula Used:

=IFERROR(VLOOKUP([Task Name]3, {Project Intake Sheet Range 3}, 7, false), "-")


Context:

Screenshot of sheet where formula lives. As you can see it isn't pulling the data from the other sheet and automatically inserts "-" or blank in fields.


Screenshot of sheet where data is being pulled from.


Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Kaye T.

    How are you populating the formula down the rows? If you're using drag-fill, this will update your original reference of [Task Name]3 to be [Task Name]4, [Task Name]5, etc as you drag.

    Try adding $ before the 3 to make sure it locks on to the 3rd cell:

    =IFERROR(VLOOKUP([Task Name]$3, {Project Intake Sheet Range 3}, 7, false), "-")

    Another thing to note is that it looks like you're bringing in two Date cells into a Text/Number field. In this instance you'll want to add "" to the end of the VLOOKUP so that it returns the date as text instead of a Date value (which will error).

    =IFERROR(VLOOKUP([Task Name]$3, {Project Intake Sheet Range 3}, 7, false) + "", "-")


    If this hasn't helped, remove the IFERROR from around your formula. It's returning "-" because there's an error, but I'd like to know which error you're getting (ex. #NO MATCH meaning it can't find the matching value of [Task Name]3 across sheets, or #INVALID COLUMN VALUE if it's trying to return a date, etc). See: Formula Error Messages

    Cheers,

    Genevieve

  • Kaye T.
    Kaye T. ✭✭
    edited 03/28/22

    Hi @Genevieve P. ,

    The error message I receive is #INCORRECT ARGUMENT SET

    Thank you for attaching the "Formula Error Messages Sheet" - I'll take a look at that now.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Kaye T.

    That's really helpful! Is it possible that your range is incorrectly selected?

    {Project Intake Sheet Range 3}

    Try recreating this range to ensure all the columns you need are included to see if this helps.

    Cheers,

    Genevieve

  • Kaye T.
    Kaye T. ✭✭

    Hi @Genevieve P. ,

    With the assistance of your resources, I was able to get the formula to work. Thanks so much for everything.

    Have a great day! 😀

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful, I'm glad to hear this! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!