Lookup value on different sheet

[EJL88]

Hello,

I am trying to get my excel sheet to lookup at value on a second sheet, but depending a value, it can be on 4 different sheets.

I found a formula where they used this:
"'Sheet ("&R14&")'!N$3"

But it does not work in my formula/array, where it returns the value error.
=INDEX("'KN"&K34&"'!B2:B40";MATCH(MIN(ABS(KN0!$A$2:$A$40-$F$34));ABS(KN0!$A$2:$A$40-$F$34);0))

Link to sheet:
https://ufile.io/khdwr

It is cell E42

 

[wideboydixon]

I think you need INDIRECT() to resolve the reference:

=INDEX(INDIRECT("'KN"&K34&"'!B2:B40"),MATCH(MIN(ABS(KN0!$A$2:$A$40-$F$34)),ABS(KN0!$A$2:$A$40-$F$34),0))

WBD

 

[EJL88]

I think you need INDIRECT() to resolve the reference:

=INDEX(INDIRECT("'KN"&K34&"'!B2:B40"),MATCH(MIN(ABS(KN0!$A$2:$A$40-$F$34)),ABS(KN0!$A$2:$A$40-$F$34),0))

WBD

Thank you very much. That solved the issue. Dunno why I forgot about INDIRECT. Spent half an hour last year google'ing what indirect was, trying to understand it :P