INDEX/COLLECT Formula Help

jl.furstenberg · 07/12/24

Hello All,

I am hoping this community can help me figure a particular scenario out:

I have 2 Sheets: Sheet A, Sheet B

I want to look at Sheet B:

Find a unique identifier in Column A (this is also the same unique identifier in Sheet A, Column A), then look at column C to see if it's blank.

If it's not blank, I want a checkbox to be checked in Column D in Sheet A.

I know that I need to use INDEX(COLLECT, but I can't seem to choose the correct range, criterion, range, etc.

Any help would be greatly appreciated!!

heyjay · 07/12/24

=IF(INDEX({Sht.A-Col.C}, MATCH(ID@row, {Sht.A-Col.A})) = "", 1, 0)

Sheet A -

Sheet B -

jl.furstenberg · 07/12/24

Hello heyjay,

Thank you for the quick response! It's close, but not quite what I am looking for:

I want the box to be checked if Sheet A Col. C is NOT blank. How would I incorporate that into your formula above?

heyjay · 07/12/24

Oh, replace = with <> .

Not Equals to Blank. You can also use IF(NOT(ISBLANK…

=IF(INDEX({Sht.A-Col.C}, MATCH(ID@row, {Sht.A-Col.A})) <> "", 1, 0)

Answers