Index match with a variable range on a dynamic choice of worksheet

[Jimbob2000]

=IFERROR(INDEX(INDIRECT("'"&$B$1&"'!"&"$E$2:$S$51"),MATCH($A5,INDIRECT("'"&$B$1&"'!"&"$A$2:$A$51"),0),MATCH($B$2,INDIRECT("'"&$B$1&"'!"&"$E$1:$S$1"),0)),"")

I'm using the formula above that uses Indirect and the value in cell B1 to determine which worksheet to pull data from. It then uses cells A5 and B2 to lookup the correct value on the sheet determined by B1 (using INDEX/MATCH).

The challenge is that the number of rows varies between the sheets that can be referenced by B1. As the formula is set up below, it has 50 rows; however, in reality it could have more or less.

I'm trying to use the INDEX/COUNTA method -- e.g., =$A$2:INDEX($A:$A,COUNTA($A:$A)) -- to allow a variable last row. The problem is that this requires me to nest two more INDIRECT sheet references for each part of the INDEX/MATCH formula and I just can figure out how to make it work.

Any ideas?

I tried something like the following but it doesn't work

=IFERROR(INDEX(INDIRECT("'"&$B$1&"'!"&"$E$2:"INDEX(INDIRECT("'"&$B$1&"'!"&"S:S"),COUNTA(INDIRECT("'"&$B$1&"'!"&"A:A")),MATCH($A5,INDIRECT("'"&$B$1&"'!"&"$A$2:"INDEX(INDIRECT("'"&$B$1&"'!"&"A:A"),COUNTA(INDIRECT("'"&$B$1&"'!"&"A:A")),0),MATCH($B$2,INDIRECT("'"&$B$1&"'!"&"$E$1:$S$1"),0)),"")

 

[sijpie]

What if you have a cell for each sheet where the number of rows on that sheet is held (using the count)

and then instead of the 51 refer to this cell
=IFERROR(INDEX(INDIRECT("'"&$B$1&"'!"&"$E$2:$S$" & C2),MATCH(.... etc