LibreOffice Calc – VLookup with dates

Posted on by rcragun

In a different post on this blog, I showed how to use Vlookup to match lists. Someone commented on that post and indicated that it didn’t work with dates. It turns out, it does, but… There is a slight tweak required to make it work. So, if you want to learn how to use VLookup, first go to that previous post. Then come back here to see how this works with dates.

The issue with dates has to do with the format of the cells. To illustrate this, I created a spreadsheet and inserted a bunch of dates:

It’s important to make clear at this point that LibreOffice Calc, just like other spreadsheet software, has different “formats” for cells. Cells can be formatted as numbers, as text, as dates, etc. This is important because LibreOffice will treat cells with different formats differently. For instance, it’s pretty challenging to do numerical calculations with text (e.g., “apple” + 65 = ?). LibreOffice needs to know the “format” of a cell. That is the key to making vlookup work with dates.

To check the format of a cell, all you need to do is right-click on it and select “Format Cells” (or Format -> Cells from the menus at the top of the screen):

Obviously, if you’re working with dates, then the format for the cells should be “Date” and whatever specific date format you want:

Okay, back to vlookup. To find a date in a list of dates, the value you are searching for also has to be a date in the Date format – ideally, they should be formatted identically. You can see in this spreadsheet, I inserted a date to search for in my list of dates:

The vlookup formula isn’t any different. In the cell just to the right of where I inserted the date I want to search for, I added my vlookup formula. Here’s what I put into my formula:

=VLOOKUP(E1,A1:A22,1,0)

This formula calls the “vlookup” function. “E1” is the date for which we are going to search in the list of dates. “A1:A22” is the list of dates in which we are searching. The “1” after that says to use the first item in the list of items we are searching for (kind of a weird requirement). And the “0” tells LibreOffice Calc that the list is not alphabetized or sorted.

Once I put that formula in, LibreOffice Calc will search through the list to see if my date is in the list. I know that my date is there. And, typically, vlookup will return the matching date if it is there, so it should return “8/27/2021”. But, here’s what I get when I hit return on my formula:

Rather than getting the target date, I get a weird number: 44435. Knowing a little bit about LibreOffice Calc, I quickly realized what the problem is: LibreOffice Calc doesn’t know that I’m searching for a date. It found the date, but the field where it is returning the value it found is currently formatted as a “Number,” not a “Date.” See:

To show that it found the matching date, the vlookup cell also needs to be formatted as a Date. So, change the formatting for the vlookup cell:

And once you hit OK, you should now see the date it found:

In short, yes, vlookup works with Dates. You just have to make sure that the cells where the vlookup formula is located are formatted as Dates not as Numbers and you’ll see which Dates match.

 608 total views

LibreOffice Calc – Comparing and Aligning Two Lists Using VLOOKUP

Posted on by rcragun

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. I have been doing this by hand but figured there had to be a way to do this in Excel. I finally figured it out but don’t want to forget how to do it. So, I’m documenting it here so I can draw on this whenever I need to do it again.

Here’s the scenario. I have a list of “items” but every month or so I receive an updated list of “items” from someone else. During that month, some of the items on my list have been taken off and some have been added to it. 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).

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). 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. Here’s how to do it quickly in LibreOffice Calc.

Here are two lists:

(NOTE: You can actually do this in two separate Calc sheets or within the same one.)

LIST 1 is in Column A and List 2 is in Column C.

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. Click in Row 2 of Column B then click on the Function Wizard (or start typing):

In the Function Wizard dialogue box, you’ll see this:

Search for VLOOKUP then double-click it and you’ll see this:

You now need to build the function. This is where I got confused, so I’m going to try to explain this carefully.

The first box is the “Search criterion.” Basically, this is what you want to find. 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.). 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. 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):

Next is the “Array” box. This is the content through which you want the software to search to find the items in LIST 1. Again, you can type this in or select it using the button to the right. However, there is an important change that you need to make here. 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. 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:

The next part is the part that threw me off for a long time in figuring this out. The “Index” is the column in the Array you want to compare to the Search Criterion. In this case, all you need to do is specify “1” since there is only one column. But, presumably, you could have an array made up of multiple columns and want to choose just the 4th column (so you would enter 4 in the Index). Here’s how this looks:

Finally, the last box that is part of our function is the “Sort order” box. This tells the software whether the lists are ordered alphabetically or not. If they are not, enter zero (“0”). If they are, enter “1.” If you leave this blank, LibreOffice Calc thinks the lists are ordered. So, make sure you fill this out.

Once you’ve got that all entered, your formula should look like what I have above. Hit “OK” and it will search the Array C2:C7 for “bananas.” If it finds it, it will return “bananas.” If it doesn’t, it will return “#N/A” as shown below:

You can then, of course, drag this formula down. When it finds the item from LIST 1 in LIST 2 it returns that item. When it doesn’t, you get #N/A.

And there you have it. A LibreOffice Calc function for searching a list for a target and returning an indicator.

(NOTE: What I typically do after I have run this function is sort by the column where the function is located so I know which items are missing from LIST 1 and which are missing from LIST 2.)

 20,403 total views,  4 views today