Use VLOOKUP and HLOOKUP to use Data Tables in Calculations

Small Excel tip today: use the Excel functions VLOOKUP and HLOOKUP to allow you to use tables of data in your calculations. You can configure these commands to “look for the closest match” or to “only find an exact match” by setting the final argument in the functions to TRUE or to FALSE.

When could this help you? Any time you’d like to automate the reading of a table of information. Or any time you can put the action you want to perform into a table form. Or, any time looking in a table of values suits better than trying to use an equation or curve-fit to predict the values.

I have 100 items to enter into this cost estimation. If I have to lookup the material multiplier each time, I'll make a mistake. Or I'll go crazy. Lets use VLOOKUP instead!

Here are some ideas to use these functions:

• When trying to analyze financial data, provide a date, and then look up the interest rates and rate of inflation on that date
• If you have a table of experimental data,  or data from a simulation program, consider pasting it into Excel and looking up the closest value. This lookup approach may be more accurate than any equation you could make up to fit the data
• Make lists of data in order, number each value, and quickly find the 5%, 50%, and 95% data points.
• When forecasting your manhour costs for a project, list out typical salaries for professional X with experience level Y, and then quickly lookup the salaries for the workers you do assign
• Provide a nominal pipe size and pipe schedule, and look up values of the selected pipe like the internal diameter of the pipe
• Automatically fill in a line list: see if you can combine the lookups with other rules to automatically pick items (maybe you can use the pipe spec to automatically fill out the surface finish, or create a table of insulation thicknesses based on pipe temperature, size, and service. I personally handle insulation this way if at all possible)
• Consider a lookup table instead of a very lengthy string of IF statements. It will be easier for you and any checkers to understand

One thing to be careful of: often you want to copy your lookup formula, and want the cells you look up to change, but not the range of cells you are doing the lookup in. (The input value changes, but the table to look inside does not move). Make sure you “lock in” the location of your table: either click on the table reference in your Excel code and hit F4, or manually add $ signs to the references. Like in the picture above. These will “lock in” the location when you copy and paste the formula.

P.S. The cost multipliers in that example picture are totally made up. Don’t use them.

2010-08-25: You might also try the OFFSET function: =Offset( range, rows, columns, height, width )