Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Matching data from spreadsheets with uneven columns

Discussion Options

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-30-2020 04:52 PM

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 fills the rows are often duplicated. What I've been doing is using VLOOKUP and using a value from spreadsheet A to find the corresponding values in spreadsheet B. This works okay, but its very slow, repetitive, and prone to error. What I'm looking to accomplish is to use something a little more automated to avoid having to either re-type the VLOOKUP formula or use the Lookup & Reference wizard each time I move to a new column. I've tried using Index Match formulas but from what I understand columns have to be the same length in order for it to work. I also looked into Power Query but in my report, I must conserve the duplicate entries. Can anyone please offer any suggestions?

Thanks!

Labels:

6 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2020 05:23 AM

I'm having a difficult time grasping the specific nature of the difficulties. You mention different numbers of columns (13 and 7) but don't say how many rows. I gather there must be many rows, given your description of VLOOKUP being slow. You don't specify the nature of the errors to which it's prone.

Anyway, if VLOOKUP works, so, I think, should INDEX -- MATCH combinations, but maybe without any advantage other than some flexibility.

Nowadays there's also the recently added **XLOOKUP**, which delivers the best of all prior lookup functions ... so give it a try.

If you still have difficulties, might I suggest that you post a sample file that either IS your actual, so long as it contains no confidential or private information, OR is a dummy file that simulates or resembles the actual.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2020 07:12 AM

@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 |

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2020 07:31 AM

Add a row above your Sheet A table with the numbers 2 through whatever

Let's say that's now row 1.

Then modify your formula so it uses the number in row 1 as the offset. Thus:

=VLOOKUP(**$A3**,B.xlsx!$A$2:$B$8,**B$1**,FALSE)

When you copy that to all the rows and columns, the highlighted cell references will change appropriately. So you only need to write the formula once; after that it's copy and paste.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2020 09:29 AM

As variant

`=IFNA(INDEX($K$4:$P$10,MATCH($B4,$J$4:$J$10,0),MATCH(C$3,$K$3:$P$3,0)),"no such")`

and drag formula on entire range.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2020 03:27 PM

@Sergei BaklanHi thanks for your reply. I guess what I was doing incorrectly is having the data in two separate workbooks. I can move the columns into the one sheet and give your formula a shot. Thanks for your help!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2020 05:36 PM

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)

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)