Forum Discussion
Consolidate/Merge Data in Multiple Files
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.
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.