Vertical search, searching the index or searching and comparing

In the free course of Excel, in Lesson 37, the vertical search function is explained but, this method has a disadvantage i.e, it can only find the value in a column which is to the right of the predefined value in a column, and we cannot use it to the left of the defined value.
In order to look up a value which is relative to a defined value and situated in the column on the left, the SEARCH function can be applied (in this example, we use the vector variant but, there is also a matrix variant).
You can download the sample file here.
The LOOKUP function with the vector version: A vector is a range that only takes one row or column. The vector variant of LOOKUP searches in a range that is only a row or a column (called a vector) for a value, goes to the corresponding position in a second vector and returns the value. Use this variant of the LOOKUP function when you want to specify a range that contains the values that are being searched for. The other variant of LOOKUP automatically searches in the first column or row.

The syntax of the vector variant of the LOOKUP function has the following arguments:
The lookup_value is mandatory. This is nthe value that LOOKUP searches for in the first vector. The lookup_value can be a number, a text, a logical value, or a name or a reference to a value.The lookup_vector is also mandatory. This is a range of cells that contain only one row or column. The values in the lookup_vector can be text, numbers or logical values.

Important:
The values in lookup_vector must in ascending order (…, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE), otherwise the function returns an incorrect result. For text values, it does not distinguish between uppercase and lowercase letters.

The result_vector is optional. This is also a range of cells that contains only one row or one column. The result_vector argument must be of the same size as lookup_vector.

The following simple example (example file sheet 1) has a column called “Letters” and a column called “Figures”. We want to find which letter goes with which figure.

We type the following formula in cell F3: = LOOKUP (A3, C3: C10, B3: B10).
We need to specify the first parameter as the lookup_value, and we refer to the lookup_value in E3.
The second parameter is the lookup_vector, which is the cells C3 to C10.
The third parameter is the result_vector, and these are the cells B3 to B10. (Remember that the result vector must be of the same size as lookup_vector)

When we type a number in cell E3, for eg., 4, and we obtain the result, D.

This formula gives a wrong result (sample file, sheet 2) when the values in the lookup_vector are not in ascending order, so we need to first sort these values in an ascending order before applying the formula, because, otherwise, we get incorrect results.

However, if we cannot (or should not) sort, we must use a different formula, namely, a formula that contains the INDEX and MATCH functions.
(Sample file, sheet 3)

We use the functions = INDEX and =MATCH to get data from the “Letters” range.

The = INDEX function has two variants namely, the reference variant and the matrix variant. In this example, we will use the reference variant.

= INDEX (reference, row_num, [column_num] [area_num]) returns a reference to a cell at the intersection of a particular row and column.

The first parameter, “reference”, (required) refers to one or more cell ranges (in our example it is the cell range “Amounts”).

The second parameter, “row number”, (required) indicates the number of the rows from which a reference value is to be retrieved.

The third parameter, “column number”, (optional) gives the number of the columns from which a reference value is to be retrieved.

The fourth parameter, “range number”, (optional) selects a range in reference from the intersection of row_num and column_num from which to return a value.

We type the formula in cell F3: = INDEX (B3: B10, MATCH (E3, C3: C10, 0))

The first parameter, “reference”, is the range of cells from which we want to retrieve the value (cells B3 through B10).
The second parameter “row number” is determined by the MATCH function. (The MATCH function searches for a specified item in a range of cells and the relative position of that item in the range is returned, and this function takes three parameters).

The Lookup_value is mandatory. The value you want to compare is in a search matrix. The search value can be a value (a number, text or logical value) or a cell reference to a number, text or logical value.

A search matrix is required. This is the range of cells that is serached.

Match_type is optional. The values are -1, 0 or 1. This argument indicates how the searched value is compared with the values in the search matrix. The default value for this argument is 1.
If the Match_type is 1 or if it is omitted, the largest value that is less than or equal to the lookup_value is searched for. The values in the lookup_array argument must be sorted in ascending order, for example … -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
If the Match_type is 0, it looks for the first value that is exactly equal to the lookup_value. The values in the lookup_array argument may be sorted in any order.
If the Match_type is -1, it looks for the smallest value that is greater than or equal to search value. The values in the lookup_array argument must be sorted in descending order, for example, TRUE, FALSE, Z-A, … 2, 1, 0, -1, -2, …, etc.).

Thus, we are looking for the position of the first value, which is exactly equal to the value in cell E3 and searching for it in the range C3 to C10.

In the example file, from sheet 4 to sheet 8 you will find other examples of searching and indexing using the MATCH function.

FYI: Swotster.com thinks of its students, so you also remember swotster.com

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats