Since we don’t want to adjust the array but rather want to search through the same items in LIST 2, we need to put dollar signs before the letters and numbers in the Array which will lock the boundaries of the array into place so they don’t change when we copy the formula to other cells, as shown below: If you select the array with your mouse and leave it as is, the values will change as you drag this formula down in Column B as it will assume you want to adjust the array as well. However, there is an important change that you need to make here. Again, you can type this in or select it using the button to the right. This is the content through which you want the software to search to find the items in LIST 1. We do this by simply selecting cell A2 (you can do this by typing it in or by selecting it by clicking the button to the right of the box and then selecting A2 like I did below): So, we are going to put in the Search criterion that we want the software to search the items in column A or LIST 1. In our example, let’s say that we want to see which items in List 1 are in List 2 (Hint: It’s apples and grapes.). The first box is the “Search criterion.” Basically, this is what you want to find. This is where I got confused, so I’m going to try to explain this carefully. Search for VLOOKUP then double-click it and you’ll see this: In the Function Wizard dialogue box, you’ll see this: Click in Row 2 of Column B then click on the Function Wizard (or start typing): In Column B I am going to create a function that allows me to search all of Column C to see which of the items in LIST 1 show up in LIST 2.

LIST 1 is in Column A and List 2 is in Column C. (NOTE: You can actually do this in two separate Calc sheets or within the same one.) Here’s how to do it quickly in LibreOffice Calc. Anyway, what that means is that I have to compare the two lists and quickly find the items on one list but not the other and vice versa. My list and the other list have to be kept synchronized but we have two separate databases that are used for this because… (argh, yeah). Likewise, the same has happened to the other list that this other person sends me (I’m being really vague here because I work at a university and there are laws that govern academic records). During that month, some of the items on my list have been taken off and some have been added to it. I have a list of “items” but every month or so I receive an updated list of “items” from someone else. So, I’m documenting it here so I can draw on this whenever I need to do it again. I finally figured it out but don’t want to forget how to do it.

I have been doing this by hand but figured there had to be a way to do this in Excel. One of the tasks I have to do regularly as part of my job is to compare two lists to see which items are missing on one list but not the other.

Vlookup compare two columns