MS Excel: VLOOKUP and HLOOKUP Function

Excel’s VLOOKUP and HLOOKUP function, which stands for vertical and horizontal lookup, is used to search for wanted information stored in sheet which could be in same or different XLS file.

If you have sheet “order” with enlisted items and sheet “prices” with items price list you can search for the prices of ordered items to calculate total amount of order.

If you have sheet “overdue” with enlisted over debt partners for sending warning letter and you have sheet “partner master data” with full partners addresses, you can search for selected addresses using these LOOKUP function.

Next will follow only VLOOKUP example, same examples will be good for understanding how HLOOKUP function work. Notice parameter separator in example bellow is (,) but depending on regional computer setting it could be used (;) instead.

How to write VLOOKUP function

=VLOOKUP(“EXAMPLE ITEM”,E5:Z15,3,False)

• “EXAMPLE ITEM” – this VLOOKUP function is looking for the price of EXAMPLE ITEM
• E5 : Z15 - it is looking for this information in the data table located in cells E5 to Z15
• 3 - VLOOKUP is looking for the price in the third column of the table
• False – indicates that only an exact match to the lookup _value ” EXAMPLE ITEM” will be accepted

The VLOOKUP function returns the results of its search – $22.76

Excel VLOOKUP Function Syntax:

lookup _value
This parameter is the value that is searched for in the first column of the table array. It could be cell reference to a value or a value itself

table_array
Range of data that is searched to find lookup_value. The first column always contains the lookup_values.

It is always a good idea to use an absolute cell reference for the table_array. If you don’t use an absolute reference and you copy the cells contain VLOOKUP function to other cells, there is a good chance you will get error messages in the cells the function is copied to or wrong data since all cells references will be change relative to original cells.

col_index_num
It’s column number of the table_array from which function will return data.

range_lookup
Logical value (TRUE or FALSE) that tells VLOOKUP to find an exact or an approximate match to the lookup_value.

• If TRUE or if this argument is omitted, VLOOKUP will use an approximate match if it cannot find an exact match to the lookup_value. If an exact match is not found, VLOOKUP uses the next largest lookup_value.
• If FALSE, VLOOKUP will only use an exact match to the lookup_value. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, an #N/A error is returned.

Excel VLOOKUP Error Messages

• If the lookup_value is not found, function returns the #N/A
• If the col_index_num argument is greater than the number of columns in table_array, function returns the #REF!