Today I will show you how to use the VLOOKUP and HLOOKUP functions. These functions exist to insert the necessary values based on data matching. For example, let’s say we have a price list with the name of the product and its price. If we want to insert the price of each product in the Price column, which can be in the same order as the price list or in any other order, this is absolutely not a critical point. This is done very simply. To do this, you need to use the VLOOKUP function.
This function will find the necessary values in the reference table and insert the values in the desired place.
Video: VLOOKUP and HLOOKUP – guide to use
VLOOKUP – Search for values in vertical tables
The function has the following syntax:
=VLOOKUP(what we are looking for; table where we are looking; ordinal number of the column from which we take the data; search principle)
Regarding the argument “search principle”, it can take only two values: TRUE (or 1) and FALSE (or 0). If the value TRUE, the matching of the values is approximate, if FALSE, the matching of the values is exact. In fact, always use FALSE or 0.
Therefore, the final formula can have the following form:
=VLOOKUP(B16;B3:C13;2;0)
Note that this is not a universal formula, and if we copy it to a range, it will give an error.
This is because some references to cells and/or cell ranges need to be converted from relative to absolute form. In other words, the formula should look like this:
=VLOOKUP(B16;B$3:C$13;2;0)
If you do not know the difference between relative and absolute references, read my article here.
HLOOKUP – Search for values in horizontal tables
The function has the same syntax as VLOOKUP, the only difference is that it searches for data not vertically (in a column), but horizontally (in a row).
Therefore, the syntax is as follows:
=HLOOKUP(what we are looking for; table where we are looking; ordinal number of the column from which we take the data; search principle)
I will also stop at the “search principle” – in 99% of cases, you need to use “0” – since this is an exact match of values, “1” is an approximate match, which is not correct in our case.
The formula will have the following form:
=HLOOKUP(B7;B$2:L$3;2;0)
Reminder: dollar signs freeze cells. For more information, read here.
VLOOKUP – Search for values in vertical tables with offset
Using EXCEL, you can solve many tasks. One of these tasks is to substitute values depending on the position. For example, the price of a product changes depending on the month of the year, and you need the price from the price list to be pulled into the data on the number of units sold. For this, we will use the same VLOOKUP function.
Only now we will slightly modernize our formula. The main difference from the previous use will be that the ordinal number of the column from which we need to take the data will contain not an absolute value, but a link to a cell where the desired number will be specified. Why is this done? – So that when the formula is shifted to the right, the formula takes data first from the 2nd, then from the 3rd, etc. columns.
In my example, the formula will be converted to the following form:
=VLOOKUP($B19;$B$4:$N$14;C$16;0)
Note that the third argument also uses an absolute reference in the row address (so that when the formula is copied down, there is no shift to subsequent rows).
File with an example of working with VLOOKUP and HLOOKUP
Lesson information file
