O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

3.11. Looking Up Values in Tables

Problem

You're using tabular data in calculations and you need an easy way to look up values in the table for use in other calculations.

Solution

Use the built-in functions Lookup, VLookup, HLookup, Match, or Index.

Discussion

Excel provides five functions to help you access data in tabular form. These functions come in handy when performing calculations that require looking up data from standard tables. For example, you can develop a spreadsheet to perform thermodynamic calculations and include standard steam tables in this spreadsheet. This way, instead of manually looking up fluid properties to use in your calculations, you can automatically include that data via the lookup functions discussed here.

LOOKUP

The syntax for LOOKUP is =LOOKUP( lookup value , lookup vector , result vector ). Lookup finds the value in result vector corresponding to the position of the value in lookup vector that matches lookup value. The lookup vector must be sorted in ascending order (see Recipe 3.9). Also, the lookup and result vectors may be row or column ranges.

You can use text or a number as the lookup value; the function just compares the lookup value to the data stored in the lookup vector to find a match. This is an important caveat with this function (and the others discussed here), as no interpolation is performed. The function looks for an exact match and if it does not find one, it returns the value in the result vector corresponding to the value in the lookup ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required