Dynamic Vlookup Formula to get information from different worksheets

=vlookup($a$10,indirect("'"&$a$10&"'!$a2:$h$10"),6,false)
or in b16 draggable across and down
=VLOOKUP($A$10,INDIRECT("'"&$A$10&"'!$A"&ROWS($A$1:$A2)&":$H$10"),COLUMNS($A$1:F$1),FALSE)

Column references within the ROW(S) functions are irrelevant and don't need to be made absolute.

ROWS(A$1:A2)

Likewise, with the COLUMN(S) functions. The row reference is irrelevant and doesn't need to be made absolute.

COLUMNS($A1:F1)

Also, quoted cell references within the INDIRECT function will never change since the reference is a text string. The INDIRECT function will automatically evaluate them as absolute references so there's no need for the $ signs.

indirect("'"&$a$10&"'!a2:h10")

INDIRECT("'"&$A$10&"'!A"&ROWS(A$1:A2)&":H10")

ROWS(A$1:A2) doesnt look as neat as =ROWS($A$1:$A2) when dragged across
like wise
=COLUMNS($A$1:F$1) looks better when dragged down

wrap the formula in iferror
=iferror(VLOOKUP($A$10,INDIRECT("'"&$A$10&"'!$A"&ROWS($A$1:$A2)&":$H$10"),COLUMNS($A$1:F$1),FALSE),"")

doesnt look as neat

All those unnecessary $ signs make the formula harder to read.

INDIRECTs with all those double and single quotes along with all the $ signs can be hard to read. If you don't need the dollar signs why include them? They have no effect whatsoever on the formula result.

well my eyes must be better then

Compared to mine, that's pretty much a guarantee.

I'm almost blind (for real).

You're welcome. Thanks for the feedback!