Indirect

Indirect is my favourite function for large excel files, as it allows us to cast the name of an excel sheet from a field and thus perform dynamic lookups.

=INDIRECT("'" & $AB1 & "'!$A$1")

This function does the same as =Sheet5!$A$1 with the difference, that Sheet5 is fetched from $AB1. As such we can directly fetch data from different sheets in multiple lines.

We can do the same for a VLOOKUP

=VLOOKUP(A1,INDIRECT("'" & $AB4 & "'!"&"$A$1:$D$4"),2,FALSE)

This is a typical VLOOKUP searching for the value of A1 in the table $A$1:$D$4 on the sheet named in $AB4. It is import to note that the range $A$1:$D$4 is also in quotes.