Checkboxes and populating data

CeeJay

Hi all, I have 3 different sheets set up.

Sheet 1 a list of names with assorted data for each name. There's a check box that will move rows to sheet 2 if checked.

Sheet 2 contains data that was moved from sheet 1.

There's a second check box column on both of these sheets, let's call it "active"

In Sheet 3, I have the same list of names that exist in sheet 1 and 2, what I want to do, is have a formala to look at the "active" column in sheet 1, if it's checked, and the name on the row on sheet 3 matches with a name on sheet 1, then return info from specific columsn, let's say Age and Address. If it doesn't find a match, then I want it to perform the same action for Sheet 2. In theory, the names should be on one of those 2 sheets, the "active" box may or may not be checked.

This is my formula that I cobbled together from a few answers I found:

=IF(INDEX({Active checkbox col-sheet 1}, MATCH(Name Cell-Sheet 3, {Name col-Sheet 1}, 0)) = 1, INDEX({Age-Sheet 1}, MATCH(Name cell-Sheet 3, {Name row-Sheet 1}, 0)), INDEX({Active checkbox col-Sheet 2}, MATCH(Name cell-Sheet 3, {Name col-Sheet 2}, 0) = 1, INDEX({Age col.-Sheet 2}, MATCH(Name, {Name Col-Sheet 2, 0))))

It's working fine for sheet 1, but it's saying no match for the checked "Active boxes" in Sheet 2.

I was thinking =if (this is true, then do this, if not then this(which is checking the other sheet)

Hopefully someone can help out.. I am hoping its just a syntax issue and I'm not asking for something that can't be done.

AravindGP

Hi @CeeJay

You're missing another IF for looking up the second sheet. What you currently have is to look up the active value in sheet 1 (your logical expression), based on it being true, you are looking for additional data from sheet 1. You've tried to add the second logical expression as a false statement, which is causing the error. Add another IF after "INDEX({Age-Sheet 1}, MATCH(Name cell-Sheet 3, {Name row-Sheet 1}, 0))", to specify your second logical expression (which is active value in sheet 2) followed by the true response, i.e., additional data from sheet 2.

CeeJay

Thanks! This is my revised, but it's still returning no match.

=IF(INDEX({Active checkbox col-sheet 1}, MATCH(Name Cell-Sheet 3, {Name col-Sheet 1}, 0)), if(index({active checkbox col-Sheet2}, Match(Name cell-Sheet 3, {Name col-Sheet 2},0)) = 1, INDEX({Age-Sheet 1}, MATCH(Name cell-Sheet 3, {Name row-Sheet 1}, 0)), INDEX({Active checkbox col-Sheet 2}, MATCH(Name cell-Sheet 3, {Name col-Sheet 2}, 0) = 1, INDEX({Age col.-Sheet 2}, MATCH(Name, {Name Col-Sheet 2, 0))))

CeeJay

Actually, updated it to this, but still #nomatch.

=IF(INDEX({Active Checkbox Col.-Sheet 1}, MATCH(Name Cell-Sheet 3, {Name Col.-Sheet 1}, 0)) = 1, INDEX({Age Col.-Sheet 1}, MATCH(Name Cell-Sheet 3, {Name Col.-Sheet 1}, 0)), IF(INDEX({Active Checkbox Col.-Sheet 2}, MATCH(Name Cell-Sheet 3, {Name Col-Sheet 2}, 0)) = 1, INDEX({Age Col-Sheet 2 }, MATCH(Name Cell-Sheet 3, {Name Col.-Sheet 2}, 0))))

Paul Newcome

Try an IFERROR.

=IFERROR(index_match_sheet_1, index_match_sheet_2)