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.

 

[alansidman]

Will you supply some dummy data that is representative of your actual data showing what you have and a mocked up solution. Please use XL2BB and no pictures as they cannot be manipulated.

 

[Kscoof]

For some reason the add-in isn't working for me. Microsoft is disabling the macros. Is there a way around this? I went to the trust center and clicked enable all macros but it still isn't allowing me to use.

 

[Kscoof]

Net Worth Template_Tax analysis Rev 2.xlsm
	B	C	D	E	F	G
1	Income	Fed Tax	Left Over Cash
2	$1.00	$0.10	$0.90
3	$2.00	$0.20	$1.80		Cash Needed	$55,000
4	$3.00	$0.30	$2.70		Total w/ Tax to Withdrawl	$62,032
5	$4.00	$0.40	$3.60
6	$5.00	$0.50	$4.50
7	$6.00	$0.60	$5.40
8	$7.00	$0.70	$6.30
9	$8.00	$0.80	$7.20
10	$9.00	$0.90	$8.10
11	$10.00	$1.00	$9.00
12	$11.00	$1.10	$9.90
13	$12.00	$1.20	$10.80
14	$13.00	$1.30	$11.70
15	$14.00	$1.40	$12.60
16	$15.00	$1.50	$13.50
17	$16.00	$1.60	$14.40
18	$17.00	$1.70	$15.30
19	$18.00	$1.80	$16.20
20	$19.00	$1.90	$17.10
21	$20.00	$2.00	$18.00
22	$21.00	$2.10	$18.90
23	$22.00	$2.20	$19.80
24	$23.00	$2.30	$20.70
25	$24.00	$2.40	$21.60
26	$25.00	$2.50	$22.50
27	$26.00	$2.60	$23.40
28	$27.00	$2.70	$24.30
29	$28.00	$2.80	$25.20
30	$29.00	$2.90	$26.10
31	$30.00	$3.00	$27.00
32	$31.00	$3.10	$27.90
33	$32.00	$3.20	$28.80
34	$33.00	$3.30	$29.70
35	$34.00	$3.40	$30.60
36	$35.00	$3.50	$31.50
37	$36.00	$3.60	$32.40
38	$37.00	$3.70	$33.30
39	$38.00	$3.80	$34.20
40	$39.00	$3.90	$35.10
41	$40.00	$4.00	$36.00
42	$41.00	$4.10	$36.90
43	$42.00	$4.20	$37.80
44	$43.00	$4.30	$38.70
45	$44.00	$4.40	$39.60
46	$45.00	$4.50	$40.50
47	$46.00	$4.60	$41.40
48	$47.00	$4.70	$42.30
49	$48.00	$4.80	$43.20
50	$49.00	$4.90	$44.10
51	$50.00	$5.00	$45.00
52	$51.00	$5.10	$45.90
53	$52.00	$5.20	$46.80
54	$53.00	$5.30	$47.70
55	$54.00	$5.40	$48.60
56	$55.00	$5.50	$49.50
57	$56.00	$5.60	$50.40
58	$57.00	$5.70	$51.30
59	$58.00	$5.80	$52.20
60	$59.00	$5.90	$53.10
61	$60.00	$6.00	$54.00
Tax Analysis Married Jointly

Cell Formulas
Range	Formula
C2:C3	C2	=IF(B2<=0,0,IF(B2<='Lookup Tables'!$B$5,B2*'Lookup Tables'!$C$4,(VLOOKUP(B2,Tax_Rate_Tbl[[Income (Married)]:[Cumulative Tax (Married)]],3,TRUE))+(((B2-VLOOKUP(B2,Tax_Rate_Tbl[[Income (Married)]:[Cumulative Tax (Married)]],1,TRUE)))*(VLOOKUP(B2,Tax_Rate_Tbl[[Income (Married)]:[Cumulative Tax (Married)]],2,TRUE)))))
D2:D3	D2	=B2-C2
B3	B3	=B2+1
G4	G4	=INDEX(Retirement_Withdrawl_Tax_Tbl[[#All],[Income]],MATCH(G3,Retirement_Withdrawl_Tax_Tbl[[#All],[Left Over Cash]],1),1)

Named Ranges
Name	Refers To	Cells
'Tax Analysis Married Jointly'!_FilterDatabase	='Tax Analysis Married Jointly'!$B$1:$C$1	G4

 

[Kscoof]

Net Worth Template_Tax analysis Rev 2.xlsm
	B	C	D	E	F	G	H	I	J
1	Fed Tax Table
2	Married Filing Jointly			Single			Head of Household
3	Income (Married)	Rate (Married)	Cumulative Tax (Married)	Income (Single)	Rate (Single)	Cumulative Tax (Single)	Income (HH)	Rate (HH)	Cumulative Tax (HH)
4	-	10.0%	-	-	10.0%	-	-	10.0%	-
5	$20,550	12.0%	$2,055	$10,275	12.0%	$1,028	$14,650	12.0%	$1,465
6	$83,550	22.0%	$9,615	$41,775	22.0%	$4,808	$55,900	22.0%	$6,415
7	$178,150	24.0%	$30,427	$89,075	24.0%	$15,214	$89,050	24.0%	$13,708
8	$340,100	32.0%	$69,295	$170,050	32.0%	$34,648	$170,050	32.0%	$33,148
9	$431,900	35.0%	$98,671	$215,950	35.0%	$49,336	$215,950	35.0%	$47,836
10	$647,850	37.0%	$174,254	$539,900	37.0%	$162,718	$539,900	37.0%	$161,219
Lookup Tables

Cell Formulas
Range	Formula
D5	D5	=[@[Income (Married)]]*C4
D6:D10	D6	=(([@[Income (Married)]]-B5)*C5)+D5
G5	G5	=[@[Income (Single)]]*F4
G6:G10	G6	=(([@[Income (Single)]]-E5)*F5)+G5
J5	J5	=[@[Income (HH)]]*I4
J6:J10	J6	=(([@[Income (HH)]]-H5)*I5)+J5

 

[Kscoof]

Just got the xl2bb add-in to working. Above is some of the data. I am using a lookup table to calculate the taxes. I didn't include the whole table as it would most likely be too big. I also only have the formula calculating tax in the top 2 rows of the look up table only, but this can be dragged down.

 

[jdellasala]

Some best practices.
Never use Merge and Center! It totally screws things up. Instead use Center Across Selection. That way the structure of the underlying table isn't changed.
Never change the alignment of numeric data with the possible exception of it being in a final report. Without formatting, numeric data ALWAYS justifies Right, text data ALWAYS justifies Left, and other data (True/False, # errors) align Center. They are instant clues to the nature of the data. When I copied your large table, for whatever reason several cells came in as text instead of numbers. It was easy to find and correct them.
Use different tables for different data sets. There's nothing WRONG with the Tax Table, and outside of PQ it might even be easier to use a single table, but even without having a PQ solution, I'm pretty sure there should be three tax tables, and not one.
As for the question at hand, LOOKUP operations are generally handled by Merging, however I don't see how at the moment, particularly since there are no matching values in the Retirement table to the Tax Table, but I'll keep looking.

 

[Kscoof]

There are three lookup tables. I just included a small part of 1. The tables max out the rows on the sheet and makes the file slow. I am working on building a table in power query with 2 million rows. I want to know how to access the table and use it in a vlookup without displaying it in the workbook.

 

[jdellasala]

There are three lookup tables. I just included a small part of 1. The tables max out the rows on the sheet and makes the file slow. I am working on building a table in power query with 2 million rows. I want to know how to access the table and use it in a vlookup without displaying it in the workbook.

While you can create a Data Model with millions of rows, you still can't load them to a Worksheet Table, only to a Pivot Table or Pivot Chart or linked to Power BI.

 

[Kscoof]

While you can create a Data Model with millions of rows, you still can't load them to a Worksheet Table, only to a Pivot Table or Pivot Chart or linked to Power BI.

While you can create a Data Model with millions of rows, you still can't load them to a Worksheet Table, only to a Pivot Table or Pivot Chart or linked to Power BI.

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.