There are two basic functions that allow you to pull data from tables you’ve created so that you can use them in a calculation: VLOOKUP and HLOOKUP.
VLOOKUP, or vertical lookup, finds a value in a row of a vertical table by matching a value in the first column. Its syntax is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value: the value in the first column that is being searched for
table_array: the table of data that is being searched
col_index_num: the column containing the values to return
range_lookup: (optional) TRUE returns an approximate match, FALSE returns an exact match
Although the range_lookup argument is optional, it’s often preferable to specify this as false, as you’ll see later in this section.
The HLOOKUP, or horizontal lookup, function is basically the same function for a horizontal table. It allows you to find a value in a column of a table by matching a value in the first row. Its syntax is nearly identical:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value: the value in the first row that is being searched for
table_array: the table of data that is being searched
row_index_num: the row containing the values to return
range_lookup: (optional) TRUE returns an approximate match, FALSE returns an exact match
We’ll do an example using the VLOOKUP function, but the same concepts can be applied to use HLOOKUP.
Worksheet 04g is set up to calculate the mass of a gas stored in a tank. In this example, there is a 10 m3 tank filled with propane at 25°C, pressurized to 5 x 105 Pa. The ideal gas equation can be used to calculate the mass of propane in the tank, but first you must look up the gas constant based on the name of the gas. VLOOKUP can be used to get this value and return it to cell C12. Begin the function:
=VLOOKUP(C5,
The name of the gas is entered in C5, so we can use this cell reference. For the second argument, select the full table (not including headers).
=VLOOKUP(C5,F7:K22,
The column index number is the number of the column containing the data we want to return. Since the gas constant data is in the third column on the table, enter the number 3 as the third argument.
=VLOOKUP(C5,F7:K22,3
Finally, to make sure that a value is returned only when the name of the gas matches a value in the first column exactly, type FALSE.
=VLOOKUP(C5,F7:K22,3,FALSE)
The data in the table is in kJ/kg*K, but the data needs to be in units of j/kg*K for the next step, so multiply the lookup value by 1000.
=VLOOKUP(C5,F7:K22,3,FALSE)*1000
Verify that the function is returning the correct result by finding the gas constant for propane in the table. Remember, it will be multiplied by 1000 in cell C12.
This worksheet already has named cells, so you can calculate the mass of gas in the tank by entering
=P*V/(Rgas*T)
Now this spreadsheet is able to calculate the mass of any gas, in any tank, under any conditions, as long as that gas is in the table. If you change the gas name in cell C5 to Argon, the result updates automatically:
What happens if the range_lookup argument is set to TRUE instead of FALSE? TRUE allows an approximate match to be returned, whereas FALSE forces an exact match. If this argument is TRUE, and the gas name in cell C5 is misspelled “Argom,” an incorrect result is returned:
This happens because Excel is trying to match the misspelled word to a value in the table. When it can’t find an exact match, it uses the first value that approximately matches. With text data, it does this alphabetically, so it returns the gas constant for “Air” in this case.
If the range lookup is FALSE and the name is misspelled, it returns an error. In this case, it’s preferable to return an error than to get an erroneous result.
