VLOOKUP – function to connect two tables

This article describe the typical use of VLOOKUP – combining values from two tables.

Example

On the left side there is a list of sales of sweets, with one missing column. This column is supposed to contain price, which is available in another column. So I want to connect the tables and put the prices from red table to the blue one.

Manual

We´ll use the VLOOKUP function and type this formula into E2:

• =VLOOKUP(B:B,G:H,2,0)

Because:

• “=” all formulas start with equal
• VLOOKUP is the name of function
• “B:B” because in B column are the names of item, that will be used for looking for prices. If you put B2 instead of B:B, it is still OK.
• “G:H” because the price list is (table we are using for assigning of values)
• “2” because we want to deliver the second column from the red table.proto, že z malé tabulky, ze které se vybírá, se má doplnit hodnota, která je ve druhém sloupečku, což je sloupeček “Cena/ks”.
• The last argument i 0 (or FALSE) usually. In some quite extraordinary situations you can use 1 (TRUE) – especially when matching number intervals or if you need the calculations to be quicker. Just keep in mind then when using 1 (TRUE), the source table (the price list) has to be sorted according the first column. Otherwise the formula provides misleading results – honestly, this is quite dangerous… If you leave this argument empty, Excels sees it as TRUE – which is little bit tricky.
• When using function dialogue, it looks like this:

And the result is:

And something more:

If the table used for assigning (the price list) was transposed (columns instead of rows) we would used HLOOKUP instead of VLOOKUP. In the third argumet there would be number of row instead number of column (but still 2…).

Why “V” in VLOOKUP? Because Vertical. H in HLOOKUP like Horizontal.

The tables can be in different sheets (and they are, very frequently…).