Nested VLOOKUP statement error

I am receiving an error with the following nested VLOOKUP statement. I've included an image that might help to better explain. Unfortunately, I have looked at this so many times and tried so many combinations that I'm afraid it is a simple fix that I am just not seeing.

Need to use the number in Details4 cell (in this case ProjectId=1) to look up on the Intake Form sheet for the Row with Project ID = 1, then go to column 50 (which is a checkbox). If the checkbox is checked then the Status field needs to say "Complete" otherwise it can say "In Queue"

Best Answers


  • Sarah Brelage
    Sarah Brelage ✭✭✭✭✭

    Awesome. It worked like a charm. I had was going back and forth about possibly using the INDEX but haven't used it much so I dismissed it quickly. Thanks for the help!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

    Honestly... I haven't used VLOOKUP in a very long time. INDEX/MATCH is SO much more flexible as it allows for reorganization of the source sheet without breaking the formula because you can reference each column individually. It also means that the order of the columns doesn't matter so you don't have to worry about which one is on the right or left.

    Here's a breakdown of how it works...

    =INDEX(range to pull from, row number, optional column number)

    The range to pull from is the obvious part. That is the data you want to pull.

    The second portion where you specify the row number is where MATCH comes into play.

    MATCH will return a numerical value based on which cell within a range specific data is found. When referencing an entire column, the returned value becomes the row number.

    =MATCH(data to search for, range to search in, match type)

    I have found that using 0 (zero) for the match type provides the most consistently accurate results.

    So if you are only looking at one column to pull data from and one column to match on, you would build the MATCH formula and just drop it into the second portion of the INDEX function.

    You can also use a second MATCH function in the third portion of the INDEX function if you are pulling from a table so that row and column numbers are both automated based on your specified criteria.

    I definitely strongly recommend familiarizing yourself with the INDEX/MATCH combo in place of the VLOOKUP as it provides a lot more flexibility and will make things easier in the long run.

    (SIDE NOTE: The COLLECT function also brings a huge level of flexibility once you get the hang of it. You can essentially use this to turn any function into a functionIF kind of thing.)

  • Sarah Brelage
    Sarah Brelage ✭✭✭✭✭

    =INDEX({Project Team Range 5}, MATCH([Responsible Dept]27, {Project Team Range 3}, 0)), 2)

    So here is the one I'm trying on my own...Project Team Range 5 is made up of two columns, Role and Name. Responsible Dept is the Role in my sheet. Project Team Range 3 is one column and it contains the many options to match with Responsible Dept. The 2 was referencing the second column in Project Team Range 5 contains the Name that needs to be returned.

    It keeps giving me an error and I've been through this several times and I matched your example etc. so I'm not stuck

  • Sarah Brelage
    Sarah Brelage ✭✭✭✭✭

    Thanks. That worked perfectly and I appreciate the explanation!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!