IF/VLOOKUP/INDEX/MATCH/COLLECT

Kenny T.

Hi all,

I have a weekly form that is required to be submitted each week a manager is in training through certification. I am working with two sheets: (1) MIT LLR (MASTER), which is sheet where all form submissions live and (2) MIT LLR Submission Tracker. The tracker sheet consists of 1 column that contains cell links to auto-populate each new manager after their start date, followed by multiple Week columns that contain check boxes to verify the form was submitted each week.

What I am looking for is a formula that will auto-check the applicable Week # column if the form was submitted for that week, but will leave blank if the manager missed a the week by referencing the Last Name and Week # in the MASTER sheet.

(1) MASTER

(2) Tracker

Kenny T.

Got it!

=IFERROR(INDEX(COLLECT({MIT ID}, {MIT ID}, [MIT ID]@row, {MIT LLR Week}, @cell = "Week 1"), 1) <> "", 0)

Paul Newcome

Got it. Ok. SO you would continue with the same syntax within the COLLECT function.

=IFERROR(INDEX(COLLECT({MIT ID}, {MIT ID}, [MIT ID]@row, {MIT LLR Week}, @cell = "Week 2", {Range}, criteria), 1) <> "", 0)

=IFERROR(INDEX(COLLECT({MIT ID}, {MIT ID}, [MIT ID]@row, {MIT LLR Week}, @cell = "Week 2", {LDL ID}, @cell = [LDL ID]@row), 1) <> "", 0)

Kenny T.

Update: I was able to figure out how to check the box based on the name using

=IFERROR(IF(INDEX({MIT Last Name}, MATCH([MIT Last Name]@row, {MIT Last Name}, 0)) <> "", 1, 0), 0)

but, cannot figure out how to add the week criteria, IF({Week},"Week 1" as an example to check off week 1 depending on the last name.

Kenny T.

Still trying, I made a helper sheet that has Week 1-10 per row in a single column to support the second part of the formula for LLR Week.

I am able to get the checkmarks for one week to work using this formula =IFERROR(IF(AND(INDEX({MIT#}, MATCH([MIT #]@row, {MIT#}, 0)) <> "", INDEX({LLR Week}, MATCH({Week 1}, {LLR Week}, 0)) <> ""), 1, 0), 0), however I can get it to differentiate by week.

Kenny T.

Also tried =IFERROR(IF(AND(INDEX({MIT#}, MATCH([MIT #]@row, {MIT#}, 0)) <> "", INDEX({LLR Week}, MATCH({Week 1}, {LLR Week}, 0)) = "Week 1"), 1, 0), 0) but am getting inconsistent returns on the checks

Paul Newcome

Try an INDEX/COLLECT in place of the INDEX/MATCH.

INDEX(COLLECT({MIT Last Name}, {MIT Last Name}, [MIT Last Name]@row, {Week}, @cell = "Week 1"), 1)

Kenny T.

Yay!!! It worked! Only thing is it returns the last name instead of a check. Totally ok with that, but would prefer the check

Kenny T.

Got it!

=IFERROR(INDEX(COLLECT({MIT ID}, {MIT ID}, [MIT ID]@row, {MIT LLR Week}, @cell = "Week 1"), 1) <> "", 0)

Kenny T.

@Paul Newcome one more qq, the trainer is also supposed to submit a form each week relevant to their MIT, is there a way to add an AND statement that evaluates the above plus the LDL ID if the form was submitted? The sheet I want to pull the data from has references for both the MIT and LDL IDs.

Paul Newcome

@Kenny T. My apologies. I didn't mean to use the INDEX/COLLECT as a stand alone. I meant to use your existing formula structure but replace each INDEX/MATCH with an INDEX/COLLECT.

As for your second question... I'm not sure I follow.

Kenny T.

@Paul Newcome, sorry I was a bit vague.

Below is the dual intake tracker that provides the weekly form validation + LDL & MIT IDs (side note - I create IDs using the auto-number option since folks may have the last name).

This sheet is where I would like to use a formula to auto-check the "WK # LDL LLR" columns using the criteria: (1) LDL ID (2) MIT ID (3) Week #

Below is the source sheet that populates the LDL form info post-submission that includes the criteria above.

Paul Newcome

I was going back through the entire thread to try to piece together exactly what you are trying to accomplish, and I noticed exactly how your INDEX/COLLECT is working. Where are you putting that formula? If you are pulling the MIT ID based on a match with [MIT ID]@row, couldn't you just do

=[MIT ID]@row

and get the same results?

Second part: I would use a COUNTIFS to count how many rows match the various range/criteria sets then drop that in an IF statement to say that if it is greater than zero then check the box.

=IF(COUNTIFS(.............)>0, 1)

Kenny T.

The formula used in WK 2 MIT LLR validates MIT#017 submitted the Week 2 form, this formula works perfect for what I needed:

=IFERROR(INDEX(COLLECT({MIT ID}, {MIT ID}, [MIT ID]@row, {MIT LLR Week}, @cell = "Week 2"), 1) <> "", 0)

I'm hoping I can do a similar formula for the WK 2 LDL LLR column, but since it is possible for an LDL to submit multiple forms in a given week for several MITs (i.e. LDL#008 will submit a weekly form for both MIT#17 & MIT#11), I need to ensure the check mark populates based on the LDL ID submitting and the applicable MID ID & Week #.

So if LDL#008 submits a form for MIT#011 + Week 2 (check) vs. if LDL#008 does not submit a form for MIT#17 + Week 2 (uncheck). Sorry this is so complicated @Paul Newcome, and I really do appreciate the help!

Paul Newcome

Ok. I think I am understanding more now. Have you tried adding another range/criteria set to the COLLECT function to evaluate the LDL?

Kenny T.

Yeah, but I wasn't sure how exactly to do that, and despite my many attempts I've been unsuccessful

Paul Newcome

Got it. Ok. SO you would continue with the same syntax within the COLLECT function.

=IFERROR(INDEX(COLLECT({MIT ID}, {MIT ID}, [MIT ID]@row, {MIT LLR Week}, @cell = "Week 2", {Range}, criteria), 1) <> "", 0)

=IFERROR(INDEX(COLLECT({MIT ID}, {MIT ID}, [MIT ID]@row, {MIT LLR Week}, @cell = "Week 2", {LDL ID}, @cell = [LDL ID]@row), 1) <> "", 0)

Kenny T.

AMAZING! YOU ARE A SMARTSHEET WIZARD!!!!! THANK YOU!