Using Vlookup and Hlookup in MS Excel
Use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the
same row of the range.
SYNTAX: =VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP])
lookup_value (required)
The value you want to look up. The value you want to look up must be in the first column of the range of cells you
specify in table-array .
table_array (required)
The range of cells in which the VLOOKUP will search for the lookup_value and the return value.
col_index_num (required)
The column number (starting with 1 for the left-most column of table-array) that contains the return value.
range_lookup (optional)
A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: TRUE
assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the
closest value. This is the default method if you don't specify one. FALSE searches for the exact value in the first
column.
The following picture shows how you'd set up your worksheet with =VLOOKUP(B8,B1:G11,6,FALSE) to return 5,300.
Using the table below:
Find the following:
Let’s try the first item.
Here’s a hint for EMP#-007:
Answer Key:
HLOOKUP searches for a value in the top row of a table or an array of values, and then returns a value in the same
column from a row you specify in the table or array.
SYNTAX: =HLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,ROW_INDEX_NUM,[RANGE_LOOKUP])
Lookup_value The value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text
string.
Table_array A table of information in which data is looked up.
Row_index_num The row number in table_array from which the matching value will be returned.
If row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error value.
Range_lookup Optional. A logical value that specifies whether you want HLOOKUP to find an exact match or an
approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not
found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match.
If one is not found, the error value #N/A is returned.
If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order: ...-2, -1, 0, 1,
2,... , A-Z, otherwise, HLOOKUP may not give the correct value.
If range_lookup is FALSE, table_array does not need to be sorted.
Example:
Using the formula highlighted in yellow below:
Here’s what will get:
Another example:
Result is:
Let’s try this formula:
Here’s what we’ll arrive at: