Matching data from spreadsheets with uneven columns

Hi, I have a report that contains about 13 columns (spreadsheet A) and I obtain data from another spreadsheet that contains only 7(spreadsheet B). The columns are of different length, and data that ...

BI & Data Analysis

excel

Formulas and Functions

Macros and VBA

power bi

Ozz_Kozz

Copper Contributor

Oct 01, 2020

mathetes Hi Thank you for your response. I will post a sample below. In the sample, I start with a sheet that contains 7 columns and 17 rows (Sheet A). The only column that has data is the Client ID column. I need to transfer the data from Sheet B to Sheet A, and also preserve the duplicates in Sheet A. Sheet B has 7 columns and 8 rows, no duplicates. What I have been doing is using a VLOOKUP formula (=VLOOKUP(A2,B.xlsx!$A$2:$B$8,2,FALSE) in each column in Sheet A to find the corresponding data in Sheet B. The problem is I have to type this formula in every column in Sheet A to get the data from Sheet B. In my actual report, there are more columns and rows, but my overall goal is to eliminate the repetition with VLOOKUP and speed up the process of transferring the data in Sheet A. Thanks!

SHEET A

Client ID	Last Name	First Name	Warehouse Price	Q. Control	Sale Price	Approved Status?
40921
40921
40921
40921
17664
17664
17664
40754
40754
79481
79481
79481
79481
98540
98540
98540

SHEET B

Client ID	Last Name	First Name	Warehouse Price	Control	Sale Price	Approved Status?
40921	Withers	Bill	50	Passed	100	Approved
17664	Mertz	Ethel	200	Not Required	500	Not Approved
40754	Simmons	Bobby	300	Passed	700	Approved
79481	Tucks	Chris	10	Passed	50	Approved
98540	Dimond	Jaime	1000	Passed	2500	Approved
99788	Piazza	Mike	20	Not Required	4000	Approved
24787	John	Tommy	800	Passed	400	Not Approved

JMB17

Bronze Contributor

Oct 02, 2020

Another variation that I believe will work for you. As mathetes pointed out, you need a $ in your first argument ($A2) so the column letter does not change when you copy the formula across (but the row is not fixed, so it will increment when you copy it down).

You could use the columns function to create a counter (since the column B is not fixed, it will increment as you copy the formula across). This relies on the columns in both workbooks being in the same order. Or, you can use the MATCH function as Segei has, but that will rely on both workbooks having the same header text.

Also, note the second argument references all of the columns of the table ($A$2:$G$8) and not just $A$2:$B$8.

=VLOOKUP($A2,B.xlsx!$A$2:$G$8,COLUMNS(B.xlsx!$A$2:B$2),FALSE)

Forum Discussion

Matching data from spreadsheets with uneven columns

Resources