Vlookup Part 2

In Part 1, we learned how to find a data point by declaring a table array or range, using a lookup column and setting which data column we needed to pull data from. That was easy enough, but most data tables are not so easy to use and as an analyst we often require lots of data quickly. To enable quick data retrieval we should employ a dynamic and hopefully scalable method. We must add dynamic abilities to our formulas to speed data capturing for reporting.

Tools:

Tools to be discussed are referencing cells for data and how to isolate the needed columns.

In part 1, we coded our formula to directly look for item 1 using the formula:

=VLOOKUP(1,$A$2:$N$12,3,False)

While this worked great as an Ad-Hoc data lookup, it fails to very be useful. References are hard coded and if using many Vlookups, there will be a lot of downtime to change every formula to generate new results.

Usually analysts need to answer multiple questions and create reports showing lots of data points. Here we will adopt a reference method to allow the formula to become dynamic.

Open and download the Vlookup P2 file to follow along. You will see I added the Report section:

The report section will allow you to change what you are looking up. I like to grey out non-input cells and leave input cells white in my user entry areas. In this case the user can change the Item # and Month. First we will discuss the Item Number reference and how this works.

I selected the item# input cell and you can see Excel references it as C16 in the top left of the screen:

You can use this as your reference id in the Vlookup formula:

Notice the Lookup_value is C16 and Excel color codes the cell reference in the formula and the corresponding cell is outlined in the same color.

The Vlookup formula is now dynamic as to what Item # you can look up. You can enter anything in cell C16, but the formula will only return data for valid references. Try entering 11 or a letter.

Your formula will return the error code #N/A.

Now that we have made the Item selectable, let’s move onto the column. Currently we are using a hard reference for the column (col_index_num).

Our current formula is:

=VLOOKUP(C16,$A$2:$N$12,3,False)

Remember: vlookup(Lookup_value, table_array,  col_index_num, [range_lookup])

Although the Col_index_num = 3, we want to make the col_index_num equal to the column position that corresponds to the Month input in Cell D16. Unlike the Item # reference, we cannot just reference Cell D16 for the col_index_num.

A brief discussion of how the C16 reference works:

Using the file linked above:

In any cell type =C16 and you will see the contents of C16 displayed.

In another cell enter =D16 and you will see the contents of D16 displayed. D16 happens to be “Jan” which is the column we want.

Notice, however, that the formula requires a number for the col_index_num, not the Month name. You could enter a number in the Month box, but this may not be the best method. Month #’s will not be the actual column position. Most people would think 1 = Jan, in this case 3 = Jan.

You could write the formula like so:

=VLOOKUP(C16,$A$2:$N$12,D16+2,False)

By using the offset adding 2 you can now select 1 for January and 2 for February, but this is not a good practice as the table may change and the +2 offset is not dynamic—meaning it will not adjust to any changes in the table.

How then will we make the Month selection dynamic? Use the Match function.

=Match(Lookup_value, Lookup_array, [Match_type])

I added a work area to the spreadsheet for trying out the match function:

When using the Match function the function looks through an array and finds if there is a match for you input and then returns the index position, which means the numerical position from start in which the data point was found.

Here we want to find the match for Jan in row 2.  Since we are using a text string we have to tell Excel it is text by encapsulating Jan in quotes “Jan”.

Note: Capitalization does not matter in Excel for matching.

&

Single quotes ‘  in front of data as well as preceding and trailing blank spaces will break a lookup/match so look for these if your data is not coming to you.

Lookup_value will be “jan”.

Lookup_array is 2:2 because the months are found in row 2.

Note: 2:2 is the range of cells that might contain the item we are looking up. This must be a single line or column—unlike a table, you can only reference a single row or single column. To declare the row as a range, you tell Excel it starts in row 2 and ends in row 2 and join the start and stop with a colon, thus 2:2. You can use the anchored version $2:$2.

[Match_type]  is optional according to the Excel hint [ ]’s means you can omit for the function to work. To generate the correct output the match_type is very important.

0 = exact match, which is what analysts usually need.

=MATCH(“jan”,2:2,0)

Again a good practice is to anchor your range/array.
.

=MATCH(“jan”,$2:$2,0)

The output of the formula is 3, which tells you the column index position is 3.

We can take the entire match formula and embed it into the vlookup in place of the 3. The new formula will look like this:

=VLOOKUP(C16,$A$2:$N$12,MATCH(D16,$2:$2,0),False)

You now have a fully dynamic lookup formula. Play with the linked file and build your own formulas.