Index, Match, Collect and/or Vlookup?

Nancy Skoulphong

Hello, may I get assistance with a formula? Thank you.

Sheet 1 ---> data in columns: Course Code, Section, Instructor

Sheet 2 ---> column names: Course Code, Section, Instructor (blank)

In sheet 2, I want to pull the instructor information from Sheet 1 based on course code and section in sheet 2.

Here's what I have but keep getting an error message. Help.

=INDEX(COLLECT({Instructor Schedule}, {Course Code Schedule}, [Course Code]@row, {Section Schedule}, Section@row, 0))

Paul Newcome

My apologies. I missed the 0. You don't need that since there is no MATCH function.

=INDEX(COLLECT({Instructor Schedule}, {Course Code Schedule}, [Course Code]@row, {Section Schedule}, Section@row), 1)

Paul Newcome

It is because the INDEX function requires a row number.

=INDEX(range to pull from, row number, optional column number)

The collect function will be the "range to pull from", and then after you close that out you will want to enter 1 for the row number.

=INDEX(COLLECT({Instructor Schedule}, {Course Code Schedule}, [Course Code]@row, {Section Schedule}, Section@row, 0), 1)

Nancy Skoulphong

Thank you for your response, Paul. I tried =INDEX(COLLECT({ClassScheduleInstructor}, {ClassScheduleCourseCode}, [Course Code]@row, {Section}, Section@row), 1) and got Invalid Value.

Nancy Skoulphong

Correction: I got #unparseable using this formula

INDEX(COLLECT({ClassScheduleInstructor}, {ClassScheduleCourseCode}, [Course Code]@row, {Section}, Section@row),0),1)

Paul Newcome

My apologies. I missed the 0. You don't need that since there is no MATCH function.

=INDEX(COLLECT({Instructor Schedule}, {Course Code Schedule}, [Course Code]@row, {Section Schedule}, Section@row), 1)

Nancy Skoulphong

The correct formula is INDEX(COLLECT({ClassScheduleInstructor}, {ClassScheduleCourseCode}, [Course Code]@row, {Section}, Section@row),1). It worked! Thank you.

Paul Newcome

Happy to help. 👍️

andri witana

hi @Paul Newcome

do u mind to check my formula?

i have similiar problem

=INDEX(COLLECT({Link SO}; {Blok}; Blok@row; {Nomor Kapling}; [Nomor Kapling]@row); {Link SO})

so i want to collect the {link SO} column from another sheet if the {blok} and {row} column is matching

the result is #invalid data type

all the column refered is text/number column

thx

Paul Newcome

@andri witana The problem is going to be with your last {Link SO} reference. That should be a delimiter such as a comma or something.

S.Karkhoff

I'm in the same predicament! I know it's something so small that I'm overlooking, but my brain is fried, and I can't figure it out!

I'm trying to populate a training date from one sheet to another based on multiple criteria in the second sheet. These are the formulas I've tried so far:

=INDEX(COLLECT({Training Date}, {Cvent Reg - Full Name}, @cell = [Full Name]@row, {Most Recent}, @cell = 1, {Attended Training},@cell = "True"), 1)

=INDEX(COLLECT({Training Date}, {Cvent Reg - Full Name}, [Full Name]@row, {Most Recent}, @cell = 1, {Attended Training}, @cell ="True"), 1)

=INDEX(COLLECT({Training Date}, {Cvent Reg - Full Name}, HAS(@cell, [Full Name]@row), {Most Recent}, @cell = 1, {Attended Training}, @cell = "True"), 1)

I get #INVALID VALUE every time! However, if I remove the {Cvent Reg - Full Name} / [Full Name]@row reference, the error message will go away (I just don't get the data I need!). Those fields are both text/number columns - not contact columns.

@Paul Newcome Any suggestions?

Paul Newcome

@S.Karkhoff Double check the range you are referencing. If there is an error in any cell being referenced, then that error can pull through into any formulas referencing it.