VLOOKUP

The VLOOKUP function consists of four parameters: lookup_value, table_array, col_index_num, and range_lookup. Here’s a breakdown of what they represent:

• lookup_value: This is the value you wish to locate in the first column of the table. In our example, it refers to Cell G2 (Value 385).
• table_array: This specifies the range of cells that contains the data table. For our purposes, it’s A14:Q1013 (this can also be dynamic, which I will explain in a future post).
• col_index_num: This indicates the column number from which you want to return a value. You can input a single number or an array of numbers. In our example, it is {1,11,3,6,7,8,9,10}. As you can see, we can specify any sequence for the columns, and the output will reflect that order.
• range_lookup: This is a boolean value that determines whether you want an exact match (FALSE) or an approximate match (TRUE) for the lookup value. In our case, it is set to FALSE. You may also use 0 for FALSE and 1 for TRUE—think of it as binary, where 0 means closed or FALSE and 1 means open or TRUE.

The VLOOKUP function looks for the lookup_value (385) in the first column of the table_array (A14:Q1013) and retrieves values from the specified columns (1, 11, 3, 6, 7, 8, 9, and 10) for each matching row. The sequence of the output columns is dictated by the arrangement of numbers in the col_index_num argument. For instance, changing it to {10, 8, 6, 7, 5, 4, 3} will yield the same values but in reverse order.

This demonstrates how VLOOKUP returns a vertical array of values from various columns for the lookup value of 385.

TRANSPOSE

The TRANSPOSE function has one parameter: array. It takes a set of cells and flips it from vertical to horizontal, or vice versa. Essentially, it transposes the rows and columns of the array.

In our example, the TRANSPOSE function takes the output from the VLOOKUP function and converts it from a vertical array into a horizontal array, making the data easier to read and analyze.