Lookup data from an Excel Table

Jan 13, 2019 | Excel Hints and Tips

Ever needed to pull together information from more than one table?  Found that you have done endless copying and pasting to pull the data together?  Thought there must be a quicker, more accurate way?  There is!  The VLOOKUP function looks up data from one table and pulls it through to another table.

Structure of the Data for a VLOOKUP

The table you are looking up the data from must contain what you are looking up in the first column and it should be unique.  A code or reference is perfect or the data could be numbered with consecutive numbers.  If the final range lookup part of the function is not used or set to true then it must also be sorted by this column otherwise it will produce some strange results.

Structure of the VLOOKUP Function

=VLOOKUP(lookup value, table array, column index number, range lookup)

Lookup value – This is what you are looking for

Table array – Where you are looking for it.  This must be in the first column of the table selected

Column index number – This is the column number that the data will be returned from with the column you are looking up being column 1

Range Lookup – This part of the function is optional.  Left blank, or when “true” is entered will look for a nearest match.  If “false” is entered it will look for exact matches only and will return an error message if the data cannot be found.  This is used more frequently.

Image of how the VLOOKUP Function works in Excel

Why does my VLOOKUP not work?

There are a number of key things to watch out for if your VLOOKUP is not producing the results you expect or an error message appears

  • When looking up numbers, if the numbers are stored as text then Excel will view them differently to normal numbers. This sometimes occurs when data is exported from other systems.  Numbers stored as text normally have a green triangle at the top left of the cell.  Click on the smart tag that appears when you are clicked on the cell.  There will be the option to convert the text to a number.
  • When looking up text, if the text has spaces after it then it will look the same but will be different.  Extra spaces at the end of the words must be removed.  The TRIM function can do this.
  • If the VLOOKUP needs to be autofilled the table array may need to be fixed so the data location doesn’t move. This can be done by using absolute referencing or range names.
  • When new data is added to the table then the range will need to be extended.  This is unless the data is formatted as a table or the full columns are used to specify the data.

Want to learn more about Excel to improve your productivity then email lara@laramellortraining.co.uk to discuss how I can help or have a look at some of the Excel Training courses I provide.