Today I spent some time looking for this trick. The function to use is called VLOOKUP. From Office Support web page:

Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number.

Function VLOOKUP requires 4 arguments. the first two are easy to understand, but the third

  1. The value to look for.
  2. The range where we want to look for the value.
  3. Index of the column in the range containing the return value.
  4. [optional] TRUE for approximate match and FALSE for a perfect match.

Let’s see an example for argument 3, from the same Office Support site:

For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.

So, this means that given a range of a single column (aka. B2:B2550) this argument will be 1. If the range includes more that one column, then it should take the index of the value to return.

The following is an example of using VLOOKUP to find the value of an item:

In my case I want to get a typical boolean TRUE / FALSE to know if the value to look for is in a given column or not. To this end I used two more functions: IF and ISERROR.

The following picture shows the use of VLOOKUP to check if the elements in column A are in column C using the 4Th argument of VLOOKUP``, set toFALSE`:

As can be see, when an element from A is not present in the C it returns #N/A (aka. an error). So we can use ISERROR to check if VLOOKUP raises an error and IF to return TRUE or FALSE:

The final formula follows:


Today I was not working using my personal computer and I was under a Windows system (Win7). I needed to use the R package ReportingTools, that used the X11 interface to create the PNG’s output, on the server side (I was working through a SSH).

What it is needed?

  1. PuTTY for the SSH connection to the server – link
  2. Xming for the XServer under Windows – link

The process

First of all the Xming should be installed and started (it will be started at the end of the installation process). The we open PuTTY:

PuTTY - X11conf

Then we open to the Connection tab, also the SSH sub-tab and finally click on X11 (left picture). In the screen check the check-box Enable X11 forwarding. The we come back to the tab Session. We type the name of the new configuration into the text-box Saved Sessions and press Save (right picture).

Load the new configuration, connect to the server where and have fun!