INDEX+MATCH: finds row+col but returns #ref (?)

[Camel123]

Hi,

{=INDEX('190708'!H2:H3333;MATCH(1;('190708'!A2:A3333=Sheet1!A3)*('190708'!B2:B3333=Sheet1!B3);0);MATCH(Sheet1!H2;'190708'!2:2;0))}

In this case, row 2 and column 8 in the chosen array, the value in that cell is 9 but excel returns #REF . What is wrong?

 

[Fluff]

At a guess, because you have a space after the sheet name in the final match

 

[JoeMo]

The "chosen array" appears to be:

'190708'!H2:H3333

containing only one column. Should that be '190708'!A2:H3333 ??

 

[Marcelo Branco]

The INDEX function refers to the eighth column (H2:H3333), so the last MATCH is not necessary.
See if this works
=INDEX('190708'!H2:H3333;MATCH(1;('190708'!A2:A3333=Sheet1!A3)*('190708'!B2:B3333=Sheet1!B3);0))
Ctrl+Shift+Enter

M.

 

[Camel123]

I added all columns to the aray rather than just one and it solved the issue, thanks to everyone. (Y)