VLOOKUP vs INDEX

VLOOKUP vs INDEX

5 May 2021 0 Door Bjorn Meijer

Vertical search

With the function VLOOKUP() you can search for a specific value in a table or range. For example, on the employee number to find the name or age of a person.

wdt_IDPersonnel numberLast nameFirst NameAgeresidence
11001de VriesJaap58Amsterdam
21002PieterseTruus42Rotterdam
31003JansenKlaas55Haarlem
41004KlaasenJan45fellow view
51005soapy waterPiet35Amsterdam

The function VLOOKUP() operates as follows:

=VLOOKUP(Search value you want to search for; the range you want to search for; the column number in the range that contains the value you want to return; returns an inexact match , indicated as 1/TRUE or 0/FALSE).

Copy the above table to Excel and paste the table in range A1:E6.

Suppose we want to know the age of Klaas Jansen with employee number 1004. In the cell where we want the answer we enter the following formula: =VLOOKUP(1004;A2:E6;4;TRUE).

In this is:

  1. 1004 the search value;
  2. A2:E6 the range you want to search in;
  3. 4 the column containing the value you want to know;
  4. TRUE indicates that you want an exact match of the search value.

The disadvantage of the function VLOOKUP() is that you can only search to the right of the search value. If you want to find the employee number based on the last name, this is with VLOOKUP() not possible. This is possible because of the function INDEX() to be combined with the function TO COMPARE().

INDEX()

The function INDEX() returns a value or the reference to a value from a table or range.

The function INDEX() operates as follows:

=INDEX(Range in which you want to search; row_number; column_number).

Suppose we want to know what the employee number is for Piet Sopjes. In the cell where we want the answer we enter the following formula: =INDEX(A2:E6;5;1).

In this is:

  1. A2:E6 the range we want to search in;
  2. 5 the row containing the value you want to know;
  3. 1 the column containing the value you want to know.

With the function INDEX() returns the value at the intersection of the row and column number.

To find the row number we can use the function TO COMPARE() to use. This function returns the relative position of the specified range (in our case 5).

The function TO COMPARE() operates as follows:

=MATCH(SearchValue; The range to search; CriterionType_Number, -1, Less Than, 0, Exact Match, 1, Greater Than).

TO COMPARE()

If we want to know on which row the name of Piet is in the specified range, we fill in the following formula: =MATCH("Pete";C2:C6;0).

In this is:

  1. Piet the value we are looking for;
  2. C2:C6 the range we are looking for;
  3. 0 is the criterion type number, in this case we want an exact match.

The result of the above formula = 5.

If we merge all functions, we arrive at the following nested formula:

=INDEX(A2:A6;MATCH("Pete";C2:C6;0))

You may notice that there is no column number entered in the INDEX() function. This is not necessary in this case, because the range consists of only one column. Had the range been defined as A2:B6, we would have had to provide a column number.

If you do not quite come out with the above explanation, you can download an example file below in which all functions can be found.