EXCEL VLOOKUP

SIMPLE EXPLANATION

VLOOKUP is useful Excel formula to scan a Revit schedule export for valid data.

For example you might import Revit area data to an Excel worksheet.

You then use a formula to reference that raw data in another worksheet that you will use as the final output.

Using VLOOKUP you can scan the Revit import data.

For example you might have a table that looks like this:

worksheet called 'Area Schedule'

You want to fill this table with data from Revit schedule exports.

So you have 3 worksheets containing raw Revit schedule export data (imported using Data>From text).

For Example:

worksheet called 'data-GBA'

In cell B2 of 'Area Schedule' the formula would be:

=VLOOKUP(A2,'data-GBA'!A1:B4,2,FALSE)

A2 = cell with value to look for

'data-GBA'!A1:B4 = range to look within

2 = column number to get value from (i.e. 2nd column in range)

FALSE = find an exact match (as opposed to closest match)

If it can't find the value you are looking for it returns an error (N/A#).

You can turn this into something else using the IFFERROR formula.

In this example the number zero is returned:

=IFERROR(VLOOKUP(A2,'data-GBA'!A1:B4,2,FALSE),0)

HINTS:

• Make your range longer than the data you have so if Revit adds extra lines the formulas will still find it.

• Put dollar signs ($) in ranges so they don't change when you copy them.

• e.g. =VLOOKUP(A2,'data-GBA'!$A$1:$B$20,2,FALSE)

FULL EXPLANATION AND EXAMPLE

VLOOKUP is easily one of Excel’s most useful functions. VLOOKUP lets Excel function more like a database than just a spreadsheet. It lets you search for a value in another location such as another series of cells or another worksheet in the Excel file. If Excel finds that value, it displays other information associated with the value.

Say you’re working on a door schedule. You extracted all your door numbers and door types from your floor plans. You would like to include a brief description of the door type in the schedule.

You could do this pretty easily by writing the description once then using copy/paste to copy it to all subsequent instances of the type. This works great except when the description changes. Then you need to copy and paste all over again. What if the descriptions are constantly changing? That’s a lot of extra work keeping up with the changes.

However, you can create a separate worksheet in your Excel file that contains a list of all the door types and descriptions. Using VLOOKUP, you create a reference to the door types table in the door schedule spreadsheet. Any changes in the door type spreadsheet will be reflected in the door schedule spreadsheet. Sounds great, right?

One key aspect of VLOOKUP is that you need a “key” or unique identifier to link one value in a spreadsheet to another value. In our door schedule example, we’ll use the door type as the key. The door schedule has a “Door Type” column as does the door type spreadsheet. Excel will use the value of the door type column to link with the door type spreadsheet and get the door description value. Let’s work through an example.

Step 1 – Create a new worksheet

Start by creating a new Excel file. By default, Excel creates a single workbook titled “Sheet1”. Clicking the “+” icon next to the sheet tab (located at the bottom of the screen) creates a new worksheet.

Step 2 – Rename worksheets

Rename “Sheet1” as “Door Schedule” and “Sheet2” as “Door Types”. These are the two worksheets we’ll work with for this example.

Step 3 – Add doors

In the “Door Schedule” worksheet, create columns for “Door #”, “Door Type” and “Description”. Add the follow data to the worksheet:

Step 4 – Add door types

Switch over to the “Door Types” worksheet and add the following data:

Step 5 – Create named range

Named ranges make it easy to refer to specific cells in a worksheet. To create a named range for the door types, start by highlighting the door types (but not the column headers) in the worksheet. Right-click and select “Define name” from the menu. Enter “Doors” as the name. This will make it easier to refer to the door types in the VLOOKUP formula.

If you add additional door types at a later date, you will need to adjust the named range. Go to Formulas > Name Manager in the ribbon. Select the “Doors” name from the list and adjust the range in the “Refers to” section.

Step 6 – Insert function

We want to get the door description based on the door type. To do this, we’ll need to insert a formula using the VLOOKUP function. Click back to the “Door Schedule” worksheet. In the “Description” column for the first door, click Formulas > Insert Function from the ribbon. Type “VLOOKUP” in the search box, select it from the list and click the “OK” button.

Step 7 – Format VLOOKUP

There are four fields you need to fill out for the VLOOKUP function:

• 1. Lookup_Value – The “key” value in the door schedule worksheet. In this example, type B2 (the current door type).

  2. Table_array – The range of cells in the door type worksheet. In this example, type the named range “Doors”.

  3. Col_index_num – The index of the column you want to display. In this example, we want to show the description information so enter “2” in the field.

  4. Range_lookup – Enter “false” if you only want exact matches or “true” if the match can be close.

Click “OK” and Excel will display the description of door type A from the Door Types worksheet.

Step 8 – Copy forumula

To apply this formula to the other doors in the door schedule worksheet, copy and paste the formula into the remaining description cells. The formula will pull each door’s description from the “Door Types” worksheet.

Test it out by changing a door’s type to type D. This will change the description to “10′ x 10′ Rolling”. Likewise, switch over to the Door Types worksheet and change a door type’s description. When you switch back to the Door Schedule worksheet, the doors of that type will have the updated description.