How to auto update Vlookup function?

[Duc1201]

Hello,

I have 2 data sheet: Sheet A and Sheet B

In the sheet A i use Vlookup to extract data from Sheet B, the sheet B is updated weekly.

In the sheet A the formula will be something like this: VLOOKUP(A3,'Sheet B'!$B$2:$D$335244,3,0)," "). Now when i clean the data in sheet B, the row will reduce from 335244 to 21000, What can i do so my formula will also auto update to D21000? (notice: the data from Sheet B is updated using Power Queries)

I know i can using the column only without using the number of row, but this is quite big data and i want to reduce the use of the cache when calculating it to prevent crash so i want to use the row instead of column.

Thank you

[#2]

@Duc1201 Well, if a Power Query generated table is the source for the pivot table, you can set the Data Source for the pivot table by the table name. Thus, rather than cell references, just use the table name.

[#3]

I dont use Pivot table for my data for now but i will try that idea with the formula, Sound good. thanks

[#4]

@Duc1201 Ooops!!! Misread. The PQ table is a structured table. In stead of absolute references use structured table references.

The formula could then look something like this:

=VLOOKUP(A3,Table1_1,3,0)