Power Query vlookup Question

[Kscoof]

Hi everyone.


Has anyone ever used a large table (2 million rows) in power query as a lookup table in a regular vlookup formula in an excel sheet? I'm not talking about a vlookup between 2 tables in power query itself. The large lookup table would be in power query and I want to use a vlookup formula in a typical excel sheet that can access the table array.

I am working on a retirement financial model that's mostly automated. In the model, I need to know how much to withdrawal from a retirement account like a 401k that will cover expenses and income taxes. For instance, if someone will need $80,000 from their 401k to cover living expenses and they only withdrawal $80K they won't have enough as they must pay income taxes on that $80K. I made a large table the breaks down income, taxes, and money left over from $1 on up. It calculates correctly, however this table makes the sheet slow. Thus, I thought putting the table in power query would help significantly. I just can't figure out how to access the lookup table when not in power query.

Any help on this issue will be much appreciated. Thanks in advance.

 

[jdellasala]

I don’t want to load the table anywhere. I want to know if it is possible to leave it in power query but still use the data in worksheet formulas. Like use the table array in a vlookup or something.

If you load the data into a Pivot Table, you can reference it from there. You'd probably want to use GetPivotData for formulas referencing the data in the PT. Set/Clear it in Options under Formulas -> Working with formulas -> Use GetPivotData functions for PivotTable references.
That said, I don't think you can do exactly what you want, and it seems like your approach isn't the best either.

 

[Kscoof]

If you load the data into a Pivot Table, you can reference it from there. You'd probably want to use GetPivotData for formulas referencing the data in the PT. Set/Clear it in Options under Formulas -> Working with formulas -> Use GetPivotData functions for PivotTable references.
That said, I don't think you can do exactly what you want, and it seems like your approach isn't the best either.

Ok, in your opinion, what is the best approach?

 

[jdellasala]

Ok, in your opinion, what is the best approach?

If you have a list of users and their income, PQ may be fine but as I said I'm not sure how to handle a "nearest" lookup, and I'd just use Formulas, particularly with 365. The tax tables themselves are tiny, so you're not talking about overburdening with formulas.

 

[datatronics505]

Hi everyone.


Has anyone ever used a large table (2 million rows) in power query as a lookup table in a regular vlookup formula in an excel sheet? I'm not talking about a vlookup between 2 tables in power query itself. The large lookup table would be in power query and I want to use a vlookup formula in a typical excel sheet that can access the table array.

I am working on a retirement financial model that's mostly automated. In the model, I need to know how much to withdrawal from a retirement account like a 401k that will cover expenses and income taxes. For instance, if someone will need $80,000 from their 401k to cover living expenses and they only withdrawal $80K they won't have enough as they must pay income taxes on that $80K. I made a large table the breaks down income, taxes, and money left over from $1 on up. It calculates correctly, however this table makes the sheet slow. Thus, I thought putting the table in power query would help significantly. I just can't figure out how to access the lookup table when not in power query.

Any help on this issue will be much appreciated. Thanks in advance.

I still don't understand why insisting on VLOOKUP when you have access to Power Query and I am guessing, to newer array-based formulas like XLOOKUP, if array-based calculations are your goal.
The condition you listed would be a great candidate for "if-then-else" Power Query formula for constructing a new column. Once you are done with calculations in Power Query, in the Close And Load dialog box choose to close and load to a table, in a new sheet. That way you'll get access to that resulting table you worked on while in Power Query. In the event that you need to edit data in the original table, the one with which you started your calculations in Power Query, you can comfortably add or delete new data and just press Refresh in the new table that was the result of your work in Power Query and the edited version will be there for you.