Help, use Excel to compare two sets of values to keep the duplicates and remove the extraneous value

New Contributor

I have two columns on different sheets of unique numerical values. I want to take the set of values from sheet A and compare it to values on sheet B, to where I can end up finding out which values are identical and present on both sheets, and which ones are not. Any advice? 

3 Replies

@chris4153021 Depending on your Excel version, look into the XLOOKUP or VLOOKUP functions.


Lookup all values from A in B and vice versa. If a matching result is returned the value exists in the other list. If not, the value does not exist in the other list.

@Riny_van_Eekelen  Thanks for the help, but I'm stuck. I don't know how to do this. I have the values in collum A and B now on the same sheet. They are order numbers. The orders in set A are a list of all order numbers belonging to me. The orders in set B are orders that were billed shipping charges, but they are from a mix of orders that are mine, and orders that are not mine. I need to figure out, using the two tables, which orders are not mine. It is the same situation as I previously described. I am not sure what to plug into xloopup to yield these results. Any help is much appreciated. Thanks

@chris4153021 Have a look at the attached file. I've entered one very basic formula in B2 and a similar one in E2. Since you have XLOOKUP, you can use the dynamic array feature that spills the results for all rows in one go. File attached.