Automatically copy cell based on 2 criteria in another sheet

maineL

I am trying to take 1 cell (Term) from my "Active" sheet and I'd like it to autopopulate my Eligibility sheet with a course name, based on the Active Sheet and the Term and name selected.

So Term has F/S and the course names can be 1 of 8 different courses. I'd like it to first recognize the Term (either F(all) or S(pring)) and then match the student name and the dropdown course selected in the Active Sheet and return the Course to the respective Fa acceptance or Sp acceptance column in my eligibility document.

Is there any way to do this? Please and thank you!

Lori

Paul Newcome

Ok. That's expected then. That's the equivalent to #NO MATCH when using an INDEX/MATCH. I usually use an IFERROR to take care of that, but wanted to make sure the base function was working properly first. The IFERROR can make it tough to troubleshoot otherwise.

=IFERROR(INDEX(COLLECT({Active TAships Course}, {Active TAships Name}, @cell = Name@row, {Active TAships Term}, @cell = "F"), 1), "")

Paul Newcome

Ok. Try this instead then:

=IF(CONTAINS("Engineering", Field@row), 14750, 10400)

Paul Newcome

Happy to help. 👍️

Andrée Starå

Hi @maineL

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

maineL

All well here. Hope with you too! I tried the Index/Match, but couldn't figure out how to have more than 1 criteria in the formula.

maineL

So attempting with the Index/Match formula I still stumble on having more than 1 criteria to meet. Ultimately, I would first like to match the student name, then the term and finally the course to populate which column (fall or spring) populates with the course to be taught. I'm scratching here...

Hollie Green

Can you share your column structure on both sheets and what your desired outcome would be in the cell with the formula. You can use fake data to screenshot.

maineL

Sure thing.

Eligibility Sheet - this one I'd like to have the Fa Acceptance / Sp Acceptance columns filled in with the course name as specified on the Active Sheet:

The Active Sheet: The term column, F, below would stipulate which column above would populate with the course name (Fa Acceptance; Sp Acceptance)

Thank you for your help!!!

Paul Newcome

Try an INDEX/COLLECT instead of an INDEX/MATCH.

=INDEX(COLLECT({Column To Pull From}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd Critieria), 1)

maineL

Hi Paul,

I tried this:

=INDEX(COLLECT({Active TAships Course}, {Name@Row}, Name, {Term}, Term="F"), 1)

But it comes back unparseable...

Paul Newcome

Make sure your {Cross Sheet References} are set up properly referring to the appropriate columns in the reference sheet.

=INDEX(COLLECT({Active TAships Course}, {Name Column}, @cell = Name@row, {Term}, @cell="F"), 1)

maineL

Ok, so I thought I had the cross sheet names all set, but I'm still unsure:

=INDEX(COLLECT({Active TAships Course}, {Active TAships Name}, Name@cell=Name@row),{Active TAships Term},Term@cell="F"), 1)

Thank you!

L

Paul Newcome

Your syntax is still off. Leave the "@cell" exactly as is from my example, and remove the closing parenthesis from after Name@row.

maineL

Thank you for your patience. I am getting closer. I now have an Invalid Value.

=INDEX(COLLECT({Active TAships Course}, {Active TAships Name}, @cell = Name@row, {Active TAships Term}, @cell = "F"), 1)

maineL

I'm also hoping if there is no data in the respective column based on the criteria, it will return a blank.

Paul Newcome

Are you getting that error for all rows, or are you only getting that error where there is not a match?

maineL

Yep. Filling in correct information if it's there, but returning Invalid Value if it's not there:

Paul Newcome

Ok. That's expected then. That's the equivalent to #NO MATCH when using an INDEX/MATCH. I usually use an IFERROR to take care of that, but wanted to make sure the base function was working properly first. The IFERROR can make it tough to troubleshoot otherwise.

=IFERROR(INDEX(COLLECT({Active TAships Course}, {Active TAships Name}, @cell = Name@row, {Active TAships Term}, @cell = "F"), 1), "")