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? ...
Is this Better? The two upper Rows of information is the available info. The third row is how I need it.
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- 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.