You are here

Home

13 - Funkctions VLOOKUP in Excel and HLOOKUP in Excel

This tutorial explains how to use VLOOKUP in Excel.

If you are not familiar with VLOOKUP function in CV we do not recommend you put a higher level of Excel skills than intermediate.

= VLOOKUP (lookup_value, table_array, col_index_num [range_lookup])

We will explain this function using the example of evaluating students test performance. Table A consists of the student´s results achieved in an exam. Table B represents Evaluation scale. Using VLOOKUP it is possible to assign students mark according to their result.

How to use Excel LOOKUP functions

Lookup_value          - the value to be searched, in this case the score of Student 1 (B15)

Table_array              - table in which the program should search for Lookup_value in this case - evaluation scale ($F$2: $G$7) - we see that the table area is fixed ($)

Col_index_num       - column number in table_array (Rating Scale), from which the formula should take grading scale (A, B, C, D, E or Fx) - (2)

[range_lookup]        - TRUE - approximate match

 

TRUE variant of VLOOKUP formula represents appropriate, most accurate match. In this case Table B (Evaluation Scale) has to be sorted ascending. If Student 1 reaches 68 points and Student 2 reaches 66 points both students will be given grade D.

 

FALSE variant takes into account only the exact match. In our case the formula will mark only those students who have achieved just as many points as is specified in table B (Rating Scale).

 It means that if Student 1 reached 60 points and Student 2 reached 63 points, to Student 1 will be assigned grade E but in the case of Student 2 formula will return an error as it was not an exact match, because the value of 63 is not defined in the Rating Scale.

 

Better example of using FALSE option is following. We want to assign a tax rate for particular company based on the country of origin.

Excel VLOOKUP example

Lookup_value              - the value to be searched - the country in which company operates (B12)

Table_array                  - table which specifies tax burden in specific country ($G$1:$H$6) - we see that the area of the lookup table is fixed while copying formula

Col_index_num          - column number in table_array (Tax Burden), from which the formula should take Tex Rate (15%,20%...) - (2)

[range_lookup]             - FALSE - exact match.

 

The Print Screen shows that the formula automatically assigns the tax rate for the company, based on the country of origin.

It is important to note that the area where is specified searched value must always be at the beginning of the table. It means that if we are looking for USA tax burden column which specifies countries of origin must be at the beginning of the table. 

 

HLOOKUP in Excel is the exact equivalent of VLOOKUP function with the difference that the VLOOKUP is looking for value according to columns and HLOOKUP searches for value according to rows. Logic and the way how to create HLOOKUP is very much the same as in the case of VLOOKUP.

Partner sites:

kosime.sk
Kosenie trávy, čistenie pozemkov a záhradnícke práce