I have a problem in which I am in need of some assistance. I am trying to merge information from 2 workbooks together. Here is what I need:Take a column of comments pertaining to order information in 3 separate columns (order#, line#1, line#2) and merge those comments into another existing workbook. I tried using Xlookup but the information isn't always transferring over properly because the same order number may apply to multiple line#'s in the order. I need the notes column to transfer according to the row that matches order#, line#1 and line#2. Is there a way when using xlookup to have a range that is being searched under lookup_value? Or another way to transfer this information to save time from manually inputting it. Thank you

bcook02 Difficult to visualise how your data is structured exactly, but you may want to look into the FILTER function in stead. Otherwise, upload a representative samples of your workbooks (eliminate any private or confidential information), noting which data should be shown where.

Riny_van_Eekelen I hope this helps explain things better. I export the data daily into Excel for various orders. What I am needing to do is transfer the comments from Workbook1 to Workbook 2 properly. I can reference the order number in the XLOOKUP but this won't necessarily transfer the comments properly because there may be more than one comment for each order #. Example: Order #124 has 3 comments because of the different part numbers but I need to transfer the comments based on the Order#, PO Item & Scheduled Quantity, not solely just based on the order number.

bcook02 Have a look at the attached simplified example. It demonstrates how you can join two lookup elements to create unique lookup values. Perhaps you can apply it to your own schedule.

Riny_van_Eekelen Thank you for that simplified XLOOKUP version. How would this be coded to pull data from one Excel file to another?

bcook02 If you have both workbooks open, you should be able to just follow all the steps and click between the workbooks to insert the lookup ranges and the return ranges. All I wanted to demonstrate is that you can use the &-sign to combine lookup values and lookup ranges.

XLOOKUP HELP IN EXCEL | Microsoft Community Hub

6 Replies

Riny_van_Eekelen
Platinum Contributor
Dec 09, 2020
bcook02 Difficult to visualise how your data is structured exactly, but you may want to look into the FILTER function in stead. Otherwise, upload a representative samples of your workbooks (eliminate any private or confidential information), noting which data should be shown where.
- bcook02
  Copper Contributor
  Dec 09, 2020
  Riny_van_Eekelen
  
  I hope this helps explain things better. I export the data daily into Excel for various orders. What I am needing to do is transfer the comments from Workbook1 to Workbook 2 properly. I can reference the order number in the XLOOKUP but this won't necessarily transfer the comments properly because there may be more than one comment for each order #.
  
  Example:
  Order #124 has 3 comments because of the different part numbers but I need to transfer the comments based on the Order#, PO Item & Scheduled Quantity, not solely just based on the order number.
  - Riny_van_Eekelen
    Platinum Contributor
    Dec 09, 2020
    bcook02 Have a look at the attached simplified example. It demonstrates how you can join two lookup elements to create unique lookup values. Perhaps you can apply it to your own schedule.
    bcook02.xlsx10 KB

Forum Discussion

XLOOKUP HELP IN EXCEL

6 Replies

Resources