In my last post, I showed how you can use web queries to pull refreshable tables of data into Excel. Now that we have the data, we can use the Excel VLOOKUP function to create formulas that will make use of this data.
Since we have a table of linear thermal expansion coefficients by material, let’s set up a spreadsheet to calculate the change in length of a beam with a change in temperature.
As you may (or may not) recall, this is the equation for linear thermal expansion:
ΔL = Change in length
α = Coefficient of Linear Thermal Expansion
L = Initial length of the beam
ΔT = Change in Temperature
The Excel VLOOKUP Function
The initial length and change in temperature are known but we want to calculate the change in length for various materials. More specifically, we want to enter the name of the material in a cell and let Excel determine the appropriate coefficient to use in the equation. We can use the data from the table created previously with a web query.
VLOOKUP is perfect for this. Per Microsoft’s own support documentation, it is used when you need to find data in a table by row. And that is exactly what we are going to do.
The syntax for VLOOKUP is:
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
The inputs are:
lookup_value: in our case, this is the material name
table_array: the table we want to get the data from
col_index_num: the column number containing the output data
range_lookup: optional, but we will set to FALSE to force an exact match lookup
OK, on to setting up the worksheet. First, I created cells for the inputs: the length, temperature change, and material name.
Create a Formula to Extract the Thermal Expansion Coefficient
Next, I set up an intermediate calculation. This is where I will use the VLOOKUP function to pull the coefficient of thermal expansion from the data table.
Let’s look at the contents of this formula:
- The first input – the “lookup_value” – references cell C5, the cell that contains the material name.
- The second input references the table of thermal expansion coefficients by material name. This table is contained on Sheet2.
- The third input, tells the VLOOKUP function to return the data in the second column of the table. This column contains the thermal expansion coefficient values.
- The fourth input – “FALSE” in this case – tells the VLOOKUP function to return a value only if it finds an exact match in the first column of the table. Otherwise, if it can’t find the value (due to a typo for instance) it will return the next alphabetical value in the table – giving us a completely erroneous result.
Since all of the values in the table were divided by 10^-6 to make it more readable, we need to multiply by that same amount to obtain the correct value for our calculation. The formula is modified as shown below:
With the formula complete, we can see that it has returned a value of 2.22E-05 m/m/K.
Just to be sure, let’s compare that to the entry in the table for Aluminum:
Calculate the Change in Length
They match, so let’s go ahead and calculate the change in length due to the temperature change:
This formula returns a result of 0.0044 m or 4.4 mm.
The Excel VLOOKUP function is such a useful tool, it should be in the toolbox of every engineer. You can build on the simple example shown above to do some pretty amazing things.
How about you? Do you have an application where you can use Excel’s VLOOKUP function to extract data from a table?