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.
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)
- Lookup value: can be a value or a cell reference
- Lookup table: logical table in which to search—in this case, another sheet
- Column offset: number of columns from the search column to be returned
- Range lookup: 0/false for exact match, 1/true for
sheer madnessfirst value less than or equal to the lookup value
- Create a new spreadsheet
- Create two sheets called People and Cars
- In the People sheet:
- Create two column headings called ID and Name
- Enter test data, e.g. 1: Rich, 2: Chris, 3: Jim
- In the Cars sheet:
- Create two column headings called Car and Owner
- Enter test data in first column, e.g. Beamer, Lambo, Jag
- In cell B2, enter
=VLOOKUP(3, People!A:B, 2, 0)
- In cell B3, enter
=VLOOKUP(2, People!A:B, 2, 0)
- 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.
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.