Primeiro de tudo, você precisa usar INDIRECT
em sua primeira fórmula.
O segundo argumento para MATCH
precisa ser um array, e você está passando uma string para ele.
Mude para
=MATCH(B$4, INDIRECT(CONCATENATE("'", A5, "'!$E:$E")), 0)
Em segundo lugar, na segunda fórmula,
você está chamando CONCATENATE
e passando seu valor de retorno para outro CONCATENATE
.
Isso é completamente desnecessário (ou seja, desnecessariamente complicado).
Você pode mudar
CONCATENATE("'", A5, "'!", CONCATENATE("E", MATCH(B$4,Sheet1!$E:$E,0)))
para
CONCATENATE("'", A5, "'!", "E", MATCH(B$4,Sheet1!$E:$E,0))
Além disso, o Excel usa &
como um operador de concatenação, para que você possa substituir o acima com
"'" & A5 & "'!" & "E" & MATCH(B$4,Sheet1!$E:$E,0)
Acho que é mais fácil de ler, mas isso é uma questão de opinião.