In Excel, how do I systematically cross-reference list data from one sheet with list data from another?

Question

Let's say I have two lists, A and B. I want to check to see if any items in list A and present in list B. Should be easy, but I've never really worked with Excel. Thank you!

The lists are super-long, can't do it visually or manually.

Answer 1

This is a rough 'n' ready solution, as you haven't said what you plan to do with the results, or if you want duplications, or if you want to count how many times each word occurs, but the following will simply let you know that an item in List A is occurs in List B.

First of all let's set up some rules. Excel needs to know where your data is, so we need to name a data 'range.'

If you click on any cells in Excel, you will see the reference of that cell in the 'Namebox' above column A (e.g. A1). Now, assuming that your List B is in cells B2 to B300, click in the Namebox and type:

B2:B300

The cells B2 to B300 should now be highlighted. Now click in the Namebox again and type "listb" - you've now given this range a name.

(Assuming the first item in List A is in Cell A2), In Cell C2, type (or copy/paste) the following:

=(VLOOKUP(A2,listb,1,0))

And then copy/paste this formula (or drag it if you know how) into the cells below of Column C until it's level with the last item in Column A.

The results should show a copy of the word in Column A if it is in Column B or "#N/A" if that word doesn't exist in Column B.

All you have to do now is to sort Column C in order, or filter the data to get your results.

Hope that helps.

Answer 2

Look for vlookup function and how it can help you. This is a very important function in Excel and its worth your time to find its myriad uses. Its whats you need to solve yoru problem.

Answer 3

Convert the vertical data to horizontal by Copy-->Paste Special-->Transpose. You should be able to reference it then.

Answer 4

I am not positive but can you not shrink each so that you can view them at the same time. Hopefully that helps