Vlookup with unknow row range

[ehus]

I am looking to do a VLOOKUP with a dynamic cell range since data will be dumped into a table as things change.

Here is my VLOOKUP: =VLOOKUP(D8,TSC_SW_Totals_data!A2:B5243,2)

The 'B5243' is where I'd like to make this the result of a COUNTA like so: =COUNTA(Table1[[#All],[Mfr ID]])

I was thinking of embedding the COUNTA into the VLOOKUP but I'm not having much luck. How can I get the number of rows from the data on the fly as the data changes.

Thanks.

[dosydos]

=VLOOKUP(D8,INDIRECT("A2:B"&COUNTA($B:$B)),2,0)

I personally don't like using Table names in my formulas. This is assuming your table is in column A and B.

A few things i noticed. You could always just do =VLOOKUP(D8,TSC_SW_Totals_data!$A:$B,2) This way you dont need the added formula and it will grab data no matter how many rows there are.

[ehus]

=VLOOKUP(D8,INDIRECT("A2:B"&COUNTA($B:$B)),2,0)

I personally don't like using Table names in my formulas. This is assuming your table is in column A and B.

A few things i noticed. You could always just do =VLOOKUP(D8,TSC_SW_Totals_data!$A:$B,2) This way you dont need the added formula and it will grab data no matter how many rows there are.

The issue I have is that the data table is in the TSC_SW_Totals_data worksheet not in the same worksheet that is performing the lookup.

[dosydos]

=VLOOKUP(D8,INDIRECT("TSC_SW_TOTALS_data!A2:B"&COUNTA(TSC_SW_TOTALS_data!$B:$B)),2,0)

[ehus]

Boom, that worked. Thanks so much.

[dosydos]

thanks for rep