Searching through data ranges

In practice, it often happens that we have tables that are in a mutual relationship. Similar to the database, the value of a table column column may be a key by which we can access the columns of another table. In this text, we will discuss the functions used to search through data ranges. If you master them, it’s quite certain that this will quickly become one of your favorite Excel tools.

For searching through data ranges we use functions VLOOKUP, HLOOKUP, and LOOKUP. The first, most commonly used, searches the tables where the records are in the rows. The second is used to search the table where the records are in the columns, and the third serves to search the value in another range of values. Let’s get started with the VLOOKUP function first. Here’s its syntax:

VLOOKUP (<value><search range/table><index>,[<search parameter>])

This function has three mandatory and one optional argument. First argument is a value we’re searching for, second is a range (usually a table) where we’re performing the search, and the third is index of column that should be used for returning the result. For example:

=VLOOKUP(A9,A1:C6,3)

is the formula by which we look for the value of cell A9 (“Marko Mikić”) in the table A1: C6, and we want to show the value of the third column in the table, which is the gross salary.

Such formulated function serves to search the sorted tables. Since this is not always the case, the fourth, optional parameter, can be specified. This is a logical value that, if true (TRUE), indicates a function to search the approximate value (as an argument we can enter a part of the name of the required value, eg “Marko”), and if not (FALSE) search is done by the exact value (the value is searched for identical to the search value). In practice, if you are not sure what effect you want to achieve, always use the fourth argument and assign it to be FALSE value.

The HLOOKUP function has the same syntax as the VLOOKUP function. The difference is that search is done by columns, so you need to set the search value, search scope, and the row index. And here, as in the previous case, we can set the optional parameter TRUE or FALSE at the end of the function.

The LOOKUP function searches the values in given range in two ways:

LOOKUP (<value>,<search range>,<result range>)

LOOKUP (<search range>,<result range>)

In the first case, the first argument is the value we are looking for. Then we quote the range to which this value is found, and the last argument states the range with the results. The entered function searches the value range until it finds the given value, and then, based on its position in the search range returns a value on the corresponding position in result range. On this occasion, it is not important whether the search and results values are found in rows or columns. E.g:

=LOOKUP(A10,A4:A8,B1:F1)

searches for “Marko Mikić” within range A4:A8. Since it is on the 5th position, as a result function returns 5th member of range B1:F1. In this example we have deliberately given result range as a row.

This function can also be used to search the range using two columns, where the first column contains the range of search values and in the second range of results (another way of using the function). In that case, enter the formula:

=LOOKUP(A10,A4:B8)

Such formula uses a function LOOKUP to search for “Marko Mikić”, and return corresponding result from range A4:B8.

In case you did not find the default value, Excel returns #N/A error. This error most often occurs when we search a table that is not sorted, and we omit to enter an optional argument function.