I'm not sure which combination of formulas I require (VLOOKUP, IF, INDEX, MATCH...)

In sheet 1 I have the columns "resource name" (text) and "onboarding paid?" (check box)

In sheet 2 I have the columns "resource name" (text), "onboard?" (check box) and "milestone paid?" (check box)

If the resource name matches, AND "onboard?" in sheet 2 is checked, I want to return the value of "milestone paid?' from sheet 2, into "onboarding paid?' in sheet 1.

It's adding in the second criterion that has me flummoxed.

Best Answer


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Hannah H

    It sounds like you'll want to build an INDEX(COLLECT to look for multiple criteria to match. Paul has a great example of this on another post (see here).

    Take a look and let me know if you need help building this out!



  • Hannah H
    Hannah H ✭✭✭✭✭

    Thanks for your reply Genevieve. I've had a look at Paul's formula and had a go, but I'm not 100% sure what I'm doing!

    Here is Pauls formula: =INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

    Here is my attempt:

    =INDEX(COLLECT({Resource Name}, {Sheet 2 Range 1}13, =1, {Sheet 2 Range 2}11, =1), 1)

    Resource Name is in sheet 1

    Sheet 2 Range 1 & 2 have the first column as Resource Name

    Position 13 is "onboard?"

    Position 11 is "milestone paid?"

    Am I correct using =1 to find the check in the checkbox?

    Thanking you!

  • Hannah H
    Hannah H ✭✭✭✭✭

    Thanks for the info Lewis. Luckily speed isn't an issue here. 😉

  • Hannah H
    Hannah H ✭✭✭✭✭

    Thank you so much Genevieve. This works perfectly. Your clear explanation really helps to cement my understanding.

  • Genevieve P.
    Genevieve P. Employee Admin

    Great! Happy to help 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!