Index / Match with Checkboxes

Mary Anne

I am trying to pull into my Team sheet summary as the "Status Phase" the value of "Project Phase" from my Status sheet when the "Final Version" checkbox has been checked. I am using the following formula but it won't read the checkbox value, it doesn't matter if it's checked or unchecked. Can't figure out what I am doing wrong.

Formula in Team sheet: =IFERROR(INDEX({STATUS - Project Phase}, MATCH(1, {STATUS - Final Version}, 0)), "")

If I change the format of my "Final Version" column in my Status sheet to be text with the option of "Yes" or "No" instead of a check box and I update my MATCH criteria from 1 to "Yes" it works.

Is there an issue trying to use the checkbox in a INDEX/MATCH formula?

Michael Culley

You can use index/match with checkbox columns but I believe you are doing it incorrectly.

You would want to do something like =INDEX({checkbox column},MATCH([unique identifier column]@row,{unique identifier on sheet with checkbox},0))

Corey Jewett

For a Checkbox column, you need to use `true` instead of `1` as your search_value in the MATCH function. (without the quotes)

JKG

@Corey Jewett can you please provide an example of the formula? I tried to replace the "1" with "true" and it did not work for me.

=INDEX({LEAP Nomination - Data Collection - Withdrawal}, MATCH({LEAP Nomination - Data Collection - NomineeEmail}, UserEmail@row, true))

vs.

=INDEX({LEAP Nomination - Data Collection - Withdrawal}, MATCH({LEAP Nomination - Data Collection - NomineeEmail}, UserEmail@row, 1))

result = #INCORRECT ARGUMENT SET

I also tried:

=INDEX({LEAP Nomination - Data Collection - Withdrawal}, MATCH(UserEmail@row, {LEAP Nomination - Data Collection - NomineeEmail}, true))

result = #INVALID DATA TYPE

Lucas Rayala

Hi @JKG, your MATCH can only have one range to search (see below for syntax). You are populating the "search type" field with "1" and/or "true". I use COLLECT instead of MATCH in the INDEX function because it's always more flexible, and you can use as many range/criteria matches as you like to narrow down the results. When you use COLLECT with INDEX, it looks like this:

=INDEX(COLLECT(range you want, criteria range 1, criteria 1, criteria range 2, criteria 2....), 1)

Probably something like this for you:"Status Phase" the value of "Project Phase" from my Status sheet when the "Final Version" 

=INDEX(COLLECT({Project Phase}, {Final Version}, 1, {LEAP Nomination - Data Collection - NomineeEmail}, UserEmail@row), 1)

It's important to put the "1" after the COLLECT function. What's happening is that the COLLECT function makes a range with ONE value (equivalent to one 'row'). The INDEX function is looking at the COLLECT results but needs to know what 'row' to access. Because you've narrowed it down to one value, you need to have it look at the first (and only) row.

Regarding the 1 vs true -- you can always use 1 or 0 as the search criteria for a checkbox. True works if you are referencing within a sheet, but does not work if you are referencing a column on a different sheet. 1/0 always works.