In this excel tutorial Rich will demonstrate a simple VLOOKUP example incorporating a named range.
The first step is to create a new spreadsheet and add data similar to the data in our example.
Step 2 is to select the range of cells we are going to perform the VLOOKUP on. A nice keyboard shortcut for that is CTRL+SHIFT+END. That does a nice job of selecting a contiguous range of data.
Next go to the name box which is just above the spreadsheet (see red box below) that currently displays a value of “A2” which is the anchor cell (top left cell) of the data we have selected.
Click into the name box and call the range “Orderinfo“. You must hit the enter key when you type a name for your range and your name can’t contain any spaces or start with a symbol like a “#” sign or anything similar. Just use plain names and no spaces. One of the things we love about name ranges is that they are globally accessible throughout the workbook without having to reference the sheet name.
Lets go over to sheet 2. We want to be able to look up the sales person based on entering the order number. Type “Order number” in cell A1 and “Sales Person” in in cell B1. In cell A2 type in the order number “8” and then in cell B2 we will type the VLOOKUP formula. Type “=” followed by “vlookup” and then open parenthesis. For the Look up value type “A2” followed by a comma.
For the table_array you only have to type the name of the name range, which is ‘Orderinfo‘. You don’t have to reference the sheet where the data is help nor do you have to reference the cell range of the data.
As you type “Orderinfo” will notice the name of the range will begin to appear in the drop down list of Excel functions, so you can arrow down to it or select it with your mouse or just type it in. Enter comma.
The col_index_num is the column number that has the value that you are trying to display. The Sales Person is listed in column 3 of the named range, so type”3” followed by a comma, and the this is an exact match to enter “false” as this is not an approximate match. Then if you hit the enter key it will return “Portia” as the sales rep. You can then type “2” in cell B2 and the VLOOKUP will return the “Sarah” as the sales person.
For more in-depth training on VLOOKUPS and Named Ranges why not consider joining one of our hands-on Excel workshops offered in Chicago and Los Angeles.