Excel Calculated Columns – How to Use VLOOKUP

You can add a calculated column to an Excel table. This uses a single formula that adjusts for each row, automatically expanding to include additional rows.

Start by inserting a new column in the table:


You need to enter the formula only once, and you won’t need to use the Fill or Copy command when the table grows.

Step 1
Click the far right column of a table, then select the Home tab, and click the arrow next to Insert – which is found in the Cells group








Step 2
Click Insert Table Columns to the Right, and then rename the new column as “Style” (i.e. style of music)











Step 3
Click anywhere in the Style column and type a formula e.g. =VLOOKUP(1,Code,2,FALSE) then press Enter – to see it automatically filled into all cells of the column













The VLOOKUP formula in this example is a vertical table lookup. It matches the value 1, in the first column of the Code table (the GenreID#). It then copies the associated value on that row in the second column of the code table (the Genre) into the Styles column. See page 90 for an example of an HLOOKUP (a horizontal lookup).




… and it’s as easy as that!


Want to know more?

For the complete guide to Excel 2016, all in the trusted In Easy Steps style, click here. Whether you’re upgrading from a previous version or new to the spreadsheet concept, Excel 2016 in easy steps will help you learn the key features constructively so you’ll be a pro in no time

See also: Operators and the Excel 2016 Calculation Sequence