Combining the Horizontal and vertical search.

In the Excel 2007 Course, in Lesson 37, we see the operation of the HLOOKUP and VLOOKUP functions.

But sometimes we want a horizontal and vertical search.

Enter January in cell C5 and press Enter.
Select cell C5 again and drag the fill handle to the right, to the cell N5.
Excel will automatically enter the names of the months of the year.
Select cells C5 through N5 and give this range a name, for eg: Months (See Excel 2007 course, Lesson 12: Range Name).

Enter a few names of sellers as examples, in cells B6 to B12 .
Give this range a name, for eg, “Sellers”.
In cells C6 to N12 you enter the amounts and then you give this range a name, such as amounts.
We are, now, going to create two drop-down lists or “Choice Lists”.
Select the cell D2, select the “Data” tab in the “Ribbon” and click the “Data Validation” button.
On the “Settings” tab (1) select “List” for the “Allow” text box (2) and (3) enter = “Sellers” in the “Source”.

Repeat this for cell D3 and enter a name in the “Source” = Months.

We enter the “INDEX” and “MATCH” functions to use data from the “Amounts” range, on request.
The “INDEX” function has two variants, namely the reference variant and the matrix variant and in this example, we will use the reference variant.
= “INDEX “(reference, row_num, [column_num] [area_num]) returns the reference to the 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 called “Amounts”).
The second parameter, “row number”, (required) indicates the row number from which a reference value has to be retrieved.
The third parameter, “column number”, (optional) gives the column number from which a reference value has to be retrieved.
The fourth parameter, “range number”, (optional) selects a range in reference from which the intersection of row_num and column_num are to be retrieved.
If we want to know how much Peter (row 6) sold in March (column 3), we can enter the formula = INDEX (Amounts, 6, 3), (we use the parameters 1,2 and 3 in this example), in the cell H2 and press Enter button and Excel will give us as a result (1) to show the cell contents of the intersection point which is located in a row 6 (2), and column 3 (3) of Amounts in the data field.

But, we go to the intersection to use the “MATCH” function. It uses a second parameter (row) and the third parameter (column).
The function = MATCH (lookup value; lookup_array; [match_type]) has 3 parameters, which are follows, Lookup value= search value
search = matrix where we search.
Match_type (optional) is 1, 0 or -1
When we haven’t specified a number or 1, the search criteria uses “MATCH” to search for the largest value that is less than or equal to search value. The values in the lookup array are to be searched in ascending order, for example … -2, -1, 0, 1, 2, …, A-Z, FALSE;
When we enter a 0, “MATCH” looks for the first value that is exactly equal to the lookup_value. The values in the lookup array may be sorted in any order.
When we enter -1, “MATCH” looks for the smallest value that is greater than or equal to lookup value. The values in the lookup array must be sorted in descending order, for example, TRUE, FALSE, Z-A, … 2, 1, 0, -1, -2,

The formula to determine where the value in cell D2, in the data field “Sellers”, (row) is located is = MATCH (D2, Sellers, 0). We give a 0 as the third parameter because we want this to be exact and the arguments (names of sellers) are in random order.
The formula to determine where the value of cell D3, in the data field “Months” (column), is located is = MATCH (D3, Months, 0)

The compound formula is: = INDEX (Amounts, MATCH (D2, Sellers, 0), MATCH (D3, Months, 0))

We can change the months or the sellers in the pull down menus and our formula adjusts the result.

Featured Courses

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