Forum Discussion
Consolidate/Merge Data in Multiple Files
I have two spreadsheets - both are lists of items with a tag number, serial number, item description, and comments (among other categories). On one of the spreadsheets, the comments column is completed but it is empty in the other spreadsheet. How can I add the comments from the spreadsheet that has them to the one that doesn't, based on what the tag number of each item is? Both spreadsheets are in a different order and some of the inventory items we have on one list do not match the other list, either because they are new inventory items or they were removed from inventory.
You can use XLOOKUP for this purpose.
Let's say the sheet with the completed comments is named Other Sheet, with tag numbers in B2:B100 and comments in D2:D100.
On the sheet without comments, let's say the tag numbers are in C2:C50.
In the comment column, enter the following formula in row 2:
=XLOOKUP(C2:C50, 'Other Sheet'!B2:B100, 'Other Sheet'!D2:D100, "")
This will automatically spill its results to rows 2 to 50.
6 Replies
But, with the order of the inventory items on each sheet being different and there being a different number of items, will that formula compare the inventory tag numbers on each sheet to make sure the comment transfers to the correct corresponding tag number's comment?
Yes, the formula searches for the tag number on the other sheet. If found, it will return the comment from the row where it found the tag number. If not found, the formula will return an empty string.
Thank you so much! I will try it when I arrive to work in the morning and let you know my outcome.
You can use XLOOKUP for this purpose.
Let's say the sheet with the completed comments is named Other Sheet, with tag numbers in B2:B100 and comments in D2:D100.
On the sheet without comments, let's say the tag numbers are in C2:C50.
In the comment column, enter the following formula in row 2:
=XLOOKUP(C2:C50, 'Other Sheet'!B2:B100, 'Other Sheet'!D2:D100, "")
This will automatically spill its results to rows 2 to 50.
I am getting a #SPILL! error. What am I doing wrong?
The formula should be entered in one cell only, and the cells below it should be empty.
