Relational databases with Excel & VLOOKUP

When a full RDBMS is overkill, you can use Excel to model relational data over multiple spreadsheets. Simple but surprisingly powerful!

Anyone who works with databases appreciates the value of raw data—the colours, borders and other embellishments people add to spreadsheets are redundant fluff that detracts from the data’s value. We like strings, integers, floats, dates and other basic datatypes in spreadsheets and we don’t like it when people insert rows that serve as headings because they’ll be lost when the data is sorted or filtered.

Bad table

Type Value
Foo Bar
Baz
Foo2 Bar2
Baz2

Good table

Type Value
Foo Bar
Foo Baz
Foo2 Bar2
Foo2 Baz2

What is VLOOKUP?

And herein lies the problem: if you’re repeating values in attempt to make a spreadsheet more data-oriented and less report-like you’ve denormalized the data by repeating values. Enter VLOOKUP.

VLOOKUP is short for “Vertical Lookup” and is used for tables with headings in the first row (whereas if headings were in the first column you’d use HLOOKUP) and it’s syntax is as follows:

=VLOOKUP(4, Clients!A:Z, 2, 0)

Parameters:

  1. Lookup value: can be a value or a cell reference
  2. Lookup table: logical table in which to search—in this case, another sheet
  3. Column offset: number of columns from the search column to be returned
  4. Range lookup: 0/false for exact match, 1/true for sheer madness first value less than or equal to the lookup value

Usage example

  1. Create a new spreadsheet
  2. Create two sheets called People and Cars
  3. In the People sheet:
    1. Create two column headings called ID and Name
    2. Enter test data, e.g. 1: Rich, 2: Chris, 3: Jim
  4. In the Cars sheet:
    1. Create two column headings called Car and Owner
    2. Enter test data in first column, e.g. Beamer, Lambo, Jag
    3. In cell B2, enter =VLOOKUP(3, People!A:B, 2, 0)
    4. In cell B3, enter =VLOOKUP(2, People!A:B, 2, 0)
    5. In cell B4, enter =VLOOKUP(1, People!A:B, 2, 0)

What just happened?!

What you’re now looking at is the value of a cell in another sheet based on the ID for its row. This allows you to use the spreadsheet like a relational database without having to store the same data in multiple places and it’s completely human-readable, so (provided people don’t start messing with vlookups) even non-technical people can use it.

Conclusion

VLOOKUP allows basic data modelling within a spreadsheet using one file per database and one sheet per table. It certainly can’t take over from a full RDBMS but for simple data (especially data that must be available to non-technical people) it’s certainly a useful tool to be aware of.

Try using this technique for managing Clients with many Projects, or for Sales and Customers for your small business or even as an accounting system with income, outgoings, clients, invoices, etc.

Leave a Reply

Your email address will not be published. Required fields are marked *