Forum Discussion
Combining two lists
Dear Forum Users, I have a question regarding fusing two lists of information in to one, i color coded them so it is clear where the information is pulled from. How do i go about this? ...
MenneW
For the 1st two cells of the third row, use the formula =A2:B2, assuming your first list has values in row 2. For the 3rd cell of the third row, you can use the formula =XLOOKUP(B10,B5,A5).
Assuming your lists each have 5 rows, the formulas might be something like =$A$2:$B$6 (typed in A20), and =XLOOKUP(B20,$B$12:$B$16,$A$12:$A$16). This assumes the 1st list goes from row 2-6 and 2nd list goes from row 12-16.
Hi Steve,
I got it to work. But I'm still running into some problems.
The lookup values do not fully match.
For example:
Search: BUHLMANN Netherlands BV.
Search matrix: BUHLMANN Netherlands B.V.
= No match because of slight name difference
How do I fix this? I already experimented with the -1 and 1, but didn't give satisfactory results
Kind regards,
Menne
I got it to work. But I'm still running into some problems.
The lookup values do not fully match.
For example:
Search: BUHLMANN Netherlands BV.
Search matrix: BUHLMANN Netherlands B.V.
= No match because of slight name difference
How do I fix this? I already experimented with the -1 and 1, but didn't give satisfactory results
Kind regards,
Menne
- Using the -1 and 1 options for the match mode is not great with a list of names, because you might get an unexpected result. Is it an option to fix the data in one of the lists where it's not an exact match?
Another option may be to match on the other fields. I notice that both the source lists have the straat, land, postcode and plaats. Assuming those are unique for each entry, it should work.- Fixing data may not be an option, we're talking about 40000 lines. I will try matching on adresses.
- You may want to consider using Power Query to merge these lists. It will avoid the need for thousands of formulas, and you can easily find out which rows don't have a match.
It's straightforward to do this. For each of your lists, you need to go to the Data tab of the ribbon, then choose "From Table/Range" to create a query for each.
Once you've created a query for each list, you can merge them as described in this article.
https://support.microsoft.com/en-us/office/create-a-fuzzy-match-power-query-ffdd5082-c0c8-4c8e-a794-bd3962b90649