Index/Match/Match

Kimberly Loveless

I have two sheets that we are using for evaluations. The first is for a high level score and the other is for specific situations. The high level one will be filled out once per application and the specific situations will be evaluated separately per application. With this I would like to pull the high level scores into each corresponding line on the specifics sheet by using the same evaluator and applicant. The formula I have is ...

=INDEX({Specific 1},MATCH([Applicant Name]@row,{Applicant},0),MATCH([Evaluator Name]@row,{Evaluator},0))

It worked when I set up the sheet but then started throw a #INVALID VALUE error for new entries while continuing to work for old entries. I completely erased the formula and switched the order of the MATCH functions and it seemed to fix it. How ever after new entries are added it is again throw the #INVALID VALUE error for only the new entries.

Any ideas on why this is happening or how this can be fixed?

Paul Newcome

Ok. There's the problem. That second MATCH function is outputting a number into the COLUMN NUMBER portion of the INDEX function.

What you need is an INDEX/COLLECT:

=INDEX(COLLECT({Specific 1}, {Applicant}, @cell = [Applicant Name]@row, {Evaluator}, @cell = [Evaluator Name]@row), 1)

Paul Newcome

Is {Evaluator} referencing a column or a row?

Kimberly Loveless

Hi @Paul Newcome all of the cross sheet references are for entire columns.

This is how the results are showing and I have it set as a column formula

Paul Newcome

Ok. There's the problem. That second MATCH function is outputting a number into the COLUMN NUMBER portion of the INDEX function.

What you need is an INDEX/COLLECT:

=INDEX(COLLECT({Specific 1}, {Applicant}, @cell = [Applicant Name]@row, {Evaluator}, @cell = [Evaluator Name]@row), 1)

Kimberly Loveless