In Excel 2007, Microsoft have given much more prominence to Tables. They are useful for formatting data, filtering and producing summary statistics, but they have another use – if you refer to a table (rather than a range) in a formula, they automatically expand when you add data!
First, create your table (select a cell in the data, then ‘Insert’ tab, ‘Tables’ group, ‘Table’ command).
The vlookup is looking for a 5 (exact match) and cannot find it - hence the error. Notice it's looking in Table1, not a range
Now, when writing a formula and wanting to include the table, just highlight it as you would when entering a range – rather than putting in the cell range, Excel will enter something like Table1
Take a look at the two graphics. The formula bar shows what’s in cell D2. Initially, the vlookup can’t find a 5 (the lookup value) so it returns an error because it’s looking for an exact match.
All I did was type in the 5 and the z - the table automatically expands, which means the vlookup can now find a 5
As soon as I type 5 and z underneath the table, the table automatically expands and the VLOOKUP now works because it can find a 5.