If-Index-Match across two sheets

simmieb

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.

Eric Law

@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")

Eric Law

@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

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