# Index, Match, Collect and/or Vlookup?

Options
✭✭✭✭
edited 08/27/20

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))

Tags:

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

Correction: I got #unparseable using this formula

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

do u mind to check my formula?

i have similiar problem

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

• ✭✭✭✭✭✭
Options

@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.

• ✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

@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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!