function has worked for 2 years, copy paste and it will no longer work

[FFFran]

Hello,
I have been using an index,match function for 2 years. Each year I create a new workbook, copy the formula and use it in the new workbook. I delete the older workbooks from my active memory and archive it.

Here is the formula
=INDEX('Students'!F:F,MATCH(1,('Students'!B:B)*(Students'!C:C),0))

Where I am on one workbook page(Workbook 1), want the formula to match the first and last names of students(Students B and C) from the Students workbook page and input data from column F into the cell with the function on Workbook 1.

I have tried restricting the range of the cells in students to a manageable number
I have changed the names of my tabs to make sure my computer is not going to the deleted file that didn't get fully erased.
I have manually typed into a cell
I have tried typing into the function bar
When I evaluate the formula it will come up with first and last name but I repeatedly get a #Value error individually in the match, but the value error show up when I click evaluate again.......

Please help.

 

[barry houdini]

That's an "array formula".

Have you confirmed with CTRL+SHFT+ENTER so that you see curly braces like { and } around the formula?

 

[FFFran]

When I try that, nothing happens.
I can't find the error.
When I evaluate the match function comes up with the first and last name of the student. I am told that the next step will result in an error. So in the evaluation I see this =INDEX('Students'!F:F,MATCH(1,"J"*"B",0)). I have removed the students complete name for confidentiality. This is actually the first student in the list.

 

[barry houdini]

If those columns ('Students'!B:B & 'Students'!C:C) contain names of students then multiplying those will always give you an error because you can't multiply text values.

Are you sure you shouldn't be comparing those names to names in other cells, e.g.

=INDEX('Students'!F:F,MATCH(1,('Students'!B:B=B2)*(Students'!C:C=C2),0))

That would be a more conventional formula........

 

[FFFran]

thank you so much for the suggestion. I have tried to do this also, but still get the same error message. I do not know why it has worked for 2 years and won't now. However when I view all functions in my original program, it is sequentially following the formula you suggest, I did not rewrite the code for each new data entry but when I started not getting answers woould just drag the formula down. The first formula looks like I wrote it but the ones in the next columns look like the one you wrote. Is there a step I can take to make that happen?

If those columns ('Students'!B:B & 'Students'!C:C) contain names of students then multiplying those will always give you an error because you can't multiply text values.

Are you sure you shouldn't be comparing those names to names in other cells, e.g.

=INDEX('Students'!F:F,MATCH(1,('Students'!B:B=B2)*(Students'!C:C=C2),0))

That would be a more conventional formula........