If-Index-Match across two sheets

Options

Hi,

I have one sheet where organizations specify their cohort date, whether or not an individual will participate in an evaluation, and other variables. In another sheet, I'd like to pull whether or not the individual has agreed to participate in the evaluation depending on the cohort date. I used the following formula that matches on a unique ID for each organization(Affiliate Partner and HFHI ID), determines if the grant year = 2022-2023, and then should return whether the individual has agreed. However, the if statement keeps evaluating to not true (i.e., I keep getting "MISSING") even when the grant year = 2022-2023. See example for affiliate with HFHI ID = 2310-2759.

IF(INDEX({Homebuyer consent form uploads Range 1}, MATCH([HFHI ID]@row, {Homebuyer consent form uploads Range 3}, 0)) = 2022 - 2023, INDEX({Homebuyer consent form uploads Range 2}, MATCH([HFHI ID]@row, {Homebuyer consent form uploads Range 3}, 0)), "MISSING")

Thanks.

Best Answer

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓
    Options

    @simmieb Try updating your formula like this.

    IF(INDEX({Homebuyer consent form uploads Range 1}, MATCH([HFHI ID]@row, {Homebuyer consent form uploads Range 3}, 0)) = "2022-2023", INDEX({Homebuyer consent form uploads Range 2}, MATCH([HFHI ID]@row, {Homebuyer consent form uploads Range 3}, 0)), "MISSING")

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓
    Options

    @simmieb Try updating your formula like this.

    IF(INDEX({Homebuyer consent form uploads Range 1}, MATCH([HFHI ID]@row, {Homebuyer consent form uploads Range 3}, 0)) = "2022-2023", INDEX({Homebuyer consent form uploads Range 2}, MATCH([HFHI ID]@row, {Homebuyer consent form uploads Range 3}, 0)), "MISSING")

  • simmieb
    simmieb ✭✭
    Options

    Thanks, Eric. I could have sworn I tried that and it didn't work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!