Dynamic list and vertical search in a dynamic area (3):

Vertical search in a dynamic area.

The VLOOKUP function is used to search in the first column of a range of cells after which, as a result, a value is returned from a cell in the same row of the range.

In cell H1, type = VLOOKUP (
The first parameter in VLOOKUP that must be specified by us is the “Search value”(22).

Click in cell F1, press the F4 function key and type a comma. So, the formula now is: = VLOOKUP ($ F $ 1;

The second parameter in VLOOKUP that must be specified by us is the “Table_array”(23).

The table array or range of cells containing data are the cells A2 to B10, in our example, but we cannot specify this because the range of cells has no fixed size, so we use the function OFFSET to specify the second parameter. So, we type OFFSET(

The first parameter that we have to give is the “Reference” (24), which is the first cell of the table array or range of cells.  Here, it is cell A2.  Click in cell A2, press the F4 function key and type a comma.

The formula is now : = VLOOKUP ($ F $ 1, OFFSET ($ A $ 2,

The second parameter that we have to specify is “Rows “(25), which is the number of rows with respect to the reference but we don’t have any rows relative to the cell A2.  So, we type zero followed by a comma.

The formula is now: = VLOOKUP ($ F $ 1, OFFSET ($ A $ 2, 0,

The third parameter that we have to specify is “Columns “(25), which is the number of columns with respect to the reference but we don’t have any columns relative to the cell A2. So, we type zero followed by a comma.

The formula is now: = VLOOKUP ($ F $ 1, OFFSET ($ A $ 2, 0, 0,

The fourth parameter that we have to give is the “Height” (27), which is how many rows the selected area should span. Here also, we let the height be determined by the COUNTA function.
We type a comma and at the end type COUNTA (
We must now give a parameter for value1(5) or select a range. We take the full range of column A.
We click on the column heading of column A, press the function key F4 and type the closing parenthesis.
We find, however, the number of cells with data from cell A2 downwards may not be counted. So type -1 (minus 1) after the closing parenthesis followed by a comma. The next parameter is “Width”.

The formula is now: = VLOOKUP ($ F $ 1, OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1;

The last parameter of the OFFSET function is the “Width” (28) which is the width of the area. The width includes 2 columns, i.e, columns A and B.
After the comma, type the number 2 and since this is the last parameter of the function types, we type the closing parenthesis and a semicolon for the third parameter of VLOOKUP.

The formula is now: = VLOOKUP ($ F $ 1, OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 2),

We now return to the third parameter of the VLOOKUP function, “col_index_num”, (29) where we need to specify the version number of the column value to be displayed.  The column where the value must be displayed is column B.  So, for the second column matrix of the table, type a comma after the number 2 followed by another commafor the last parameter of the VLOOKUP function.

The formula is now:
= VLOOKUP ($ F $ 1, OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 2), 2,

For the last parameter, “range_lookup”, (30) we must specify whether the exact or approximate matches must be sought.  We select whether to be precise in our search or not by specifying this parameter and type a 0 (zero) followed by the closing parenthesis of the VLOOKUP function.

The formula is now:
= VLOOKUP ($ F $ 1, OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 2), 2, 0)
We conclude by pressing Enter.

The results of the formula:

The cell format for cell H1, in this example is: # “km”

Part 1 – Part 2 – Part 3

Copyright © 2013 – Swotster Ltd.

Featured Courses

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