Comparision of Data (IF)

Have a look at the attached. You can use an index/match formula in Column D of your "New" tab to search for the component number in the relevant column in the "Legacy" tab, and return the component number if it finds it; otherwise, it will return #N/A, or a custom message of your choosing, eg "NOT IN LEGACY". You'll need to do something about the numbers stored as text (rather than numbers) in the component column of "Legacy" , though, otherwise that won't work.

Please note I had to change some of your data to show you that this works - C6 & C12 have been changed on the "New" tab.


The second part of your question seems to be a fairly straightforward situation requiring the use of a countif formula; however be aware that as some of your component numbers are repeated (eg 801041) the number of components is repeated, also. You might be better off generating a list of components in which no component number is repeated, and working off that to get the result you are looking for in Column E.


Why don't you see if you can manage the last part of your question yourself, based on what's above?