Vlookup help and alternative

[reporting_95]

Hello,
In my reports, I am using vlookup to fill blank rows and columns with data that comes from a different workbook. This works but its tidious to have to either type in the formula in each cell or use the Lookup & Reference option in each column. Please see the example below:
This the full data workbook:

Client ID	Last Name	First Name	Item	Cost
515155	Smith	John	Book	$1.50
515156	Doe	Jane	Box	$10.50
515158	Hayes	Charles	Chair	$11.50
515158	Rodriguez	Alex	Baseball	$5.00
515159	Jeter	Derek	Glove	$30.00
515160	Jordan	Michael	Sneakers	$150.00
515161	Mertz	Ethel	Dress	$100.00
515162	Draper	Don	Suit	$1,000.00
515163	Walker	Karen	Purse	$500.00

And this is my report:

Client ID	Year of Purchase	Sale Type	Last Name	First Name	Item	Cost
515158	2020	Final	Hayes	Charles	Chair	$11.50
515162	2020	Final	Draper	Don	Suit	$1,000.00
515159	2020	Final	Jeter	Derek	Glove	$30.00
515163	2020	Final	Walker	Karen	Purse	$500.00
515156	2020	Final	Doe	Jane	Box	$10.50
515161	2020	Final	Mertz	Ethel	Dress	$100.00
515160	2020	Final	Jordan	Michael	Sneakers	$150.00
515160	2020	Final	Jordan	Michael	Sneakers	$150.00

I use the client ID in vlookup to fill the columns in my report. The formula I use is =VLOOKUP(A2,[Book1]Sheet1!$A$2:$B$10,2,FALSE). Is there an easier, more efficient way to get the data in my report without having to use vlookup all the time?

Thanks!

 

[alansidman]

If you post your second table, I can probably show you how to do this in Power Query by joining the two tables. Fairly quick and simple to do.

 

[reporting_95]

If you post your second table, I can probably show you how to do this in Power Query by joining the two tables. Fairly quick and simple to do.

Hi alansidman,

Below is the second table, or better yet what my report would look like. I purposely added 2 additional columns that are not in the first table because there are times I have to manually insert columns with fill data. Also, you'll see there are some names that appear twice. That's also done by design, because there are also times my report must show the duplicates.

Client ID	Year of Purchase	Sale Type	Last Name	First Name	Item	Cost
515158	2020	Final	Hayes	Charles	Chair	$11.50
515162	2020	Final	Draper	Don	Suit	$1,000.00
515159	2020	Final	Jeter	Derek	Glove	$30.00
515163	2020	Final	Walker	Karen	Purse	$500.00
515156	2020	Final	Doe	Jane	Box	$10.50
515161	2020	Final	Mertz	Ethel	Dress	$100.00
515160	2020	Final	Jordan	Michael	Sneakers	$150.00
515160	2020	Final	Jordan	Michael	Sneakers	$150.00

Thanks!

 

[reporting_95]

If the columns have to match in order for Power Query to work, I can always add the manual columns after the data has been matched from the first workbook to my report.

 

[reporting_95]

If you post your second table, I can probably show you how to do this in Power Query by joining the two tables. Fairly quick and simple to do.

Sorry for the double message, but I noticed I posted the second table with data already filled in. I'll repost both tables.

First table is the the workbook I am getting my data from

Client ID	Last Name	First Name	Item	Cost
515155	Smith	John	Book	$1.50
515156	Doe	Jane	Box	$10.50
515158	Hayes	Charles	Chair	$11.50
515158	Rodriguez	Alex	Baseball	$5.00
515159	Jeter	Derek	Glove	$30.00
515160	Jordan	Michael	Sneakers	$150.00
515161	Mertz	Ethel	Dress	$100.00
515162	Draper	Don	Suit	$1,000.00
515163	Walker	Karen	Purse	$500.00

Second table is my report

Client ID	Year of Purchase	Sale Type	Last Name	First Name	Item	Cost
515158	2020	Final
515162	2020	Final
515159	2020	Final
515163	2020	Final
515156	2020	Final
515161	2020	Final
515160	2020	Final
515160	2020	Final

The vlookup formula I would use is =VLOOKUP(A2,[Book1]Sheet1!$A$2:$B$10,2,FALSE) to get the last name by match the client IDs, and then I'd run the formula again changing the column number to 3 for first name, 4 for item, and so on.

Thanks again!

 

[alansidman]

Using Power Query/Get and Transform which is located on the Data Tab,. Bring each table into PQ Editor and join them using the Mcode shown below.

Book2
	A	B	C	D	E	F	G
1	Client ID	Year of Purchase	Sale Type	Last Name	First Name	Item	Cost
2	515158	2020	Final	Hayes	Charles	Chair	11.5
3	515158	2020	Final	Rodriguez	Alex	Baseball	5
4	515162	2020	Final	Draper	Don	Suit	1000
5	515156	2020	Final	Doe	Jane	Box	10.5
6	515159	2020	Final	Jeter	Derek	Glove	30
7	515163	2020	Final	Walker	Karen	Purse	500
8	515161	2020	Final	Mertz	Ethel	Dress	100
9	515160	2020	Final	Jordan	Michael	Sneakers	150
10	515160	2020	Final	Jordan	Michael	Sneakers	150
Sheet3

let
    Source = Table.NestedJoin(Table2, {"Client ID"}, Table1, {"Client ID"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Last Name", "First Name", "Item", "Cost"}, {"Last Name", "First Name", "Item", "Cost"})
in
    #"Expanded Table1"

 

[reporting_95]

I get the error "Expression.Error: A cyclic reference was encountered during evaluation." when I enter the Mcode. Let me detail the steps I take:

-I put both worksheets in one workbook, table them and named Table1 and Table2 respectively, save workbook under name "Book2"
-On the Excel ribbon I go to Data>New Query>Combine Queries>Launch Query Editor
-Under the Home tab of the PQ Editor, I go to New Source>File>Excel>Book2>Open
-The Navigator opens, I check "Select multiple items" and select Table1 and Table2>OK
-Then I go to View>Advanced Editor>Delete current code and replace with the code you provided>Done

This is where I receive the above error.
Thanks!

 

[alansidman]

Here is how I did it.
1. Loaded each table to PQ (note for table 2, I only loaded columns A,B and C)
2. Close and Loaded each to Connection only
3. Merged the two tables with the Mcode shown.
4. Close and Load to a Worksheet.

Your method should provide same results, but not sure why it isn't and what is causing the error message.