Combining two lists

Question

Dear Forum Users,&nbsp;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?&nbsp;&nbsp;

steve_sumproductcom · Answer

Please show how you want this data to look after the lists are fused into one.

mennew · Answer

Steve_SumProductCom&nbsp;Is this Better? The two upper Rows of information is the available info. The third row is how I need it.

steve_sumproductcom · Answer

MenneW&nbsp;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&nbsp;=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.

mennew · Answer

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 differenceHow do I fix this? I already experimented with the -1 and 1, but didn't give satisfactory resultsKind regards,Menne

steve_sumproductcom · Answer

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.

Forum Discussion

Combining two lists

7 Replies

Resources