Excel – Tips and Tricks

  1. Home
  2. /
  3. Excel – Tips and Tricks

Excel – Tips and Tricks

Vlookup

Easily one of the most often used and most often misunderstood functions in Excel is Vlookup.

Lots of people shy away from this function because they think it is too complex, it is not.

The Excel VLOOKUP function is used to look for specific data in a Spreadsheet or range using the first column of the range as an index.

In simple terms let´s suppose that we have a list of products in column A of a Spreadsheet, and each product has various attributes. These attributes are entered in columns B through to column Z. The data could be size, weight, color, price etc., in fact this is the Company`s Technical Table. Conveniently it has been given the Excel named range of Technical_Table.

On a second Spreadsheet, the Sales Table, we have the same list of  Products but with no attributes.  What we need to add is color and price. We could use copy and paste, but what happens if the price, or any of the other attributes changes.

A B C
1 Product Color Price
2 Widgets Blue $10
3 Bolts Inox $1
4 Screws Inox $0.50
5

=vlookup($A2,Technical_Table,3,0)

A Translation:

  1. Take the value in cell A2, (Widgets)
  2. Go to the Technical Table and find the row that contains Widgets in the first column of the Technical Table, (The Product Column)
  3. Bring me back the value that is in the 3rd column of that row, (the Price Column)
  4. By the way, I want an exact match for the value.
Translate »