Index Match Over Array with 3 Criteria

Hi!

I am trying to make an index match that spans an array and has 3 criteria.

I have survey data that I need to score. I have created a key that has the (1) column for question number, (2) column for "score" or "scale" and, (3) header that has the coded value for each option (essentially, the number I see when I export the raw data). This spans an array (as each question has multiple options).

Under the section titled "Scored Data", I have highlighted a space where I would like the scored numbers to populate, based on the corresponding numbers in the raw data section. I would like to match (1) Question (2) "Score" (3) Raw Data Number. I know this is possible, but I keep getting an Error (even when entering as array)!

=INDEX(Analysis!$J$14:$R$19,MATCH($A6&"Score"&B6,$H$14:$H$19&$I$14:$I$19&$J$13:$R$13,0)) = #N/A Error
See file: Index Match 3 Criteria Error.xlsx

Ideally, I would like to keep the addition of rows/columns to a minimum, since the data is already meaty. This is why I am hoping to concatenate inside the formula, as opposed to creating new cells.

Please let me know if you can help!

Celeste

Hi Celeste,

Please see below array formula

=INDEX(Analysis!$J$14:$R$19,MATCH($A6&"Score",$H$14:$H$19&$I$14:$I$19,0),MATCH(""&B6,$J$13:$R$13,0))


Thanks and Regards
Roneil

Hi Ford,

I thought this is an array formula.if you look at my formula, it is same as yours, the only difference is you use Text formula in order to convert number to text format, i use ""&. I think text is longer than ""&.

still the result is same so i have no problem with it.thanks for your formula

Regards,
Roneil

hi Ford

Thanks a lot.

Regards,
Roneil