Vlookup, hlookup, match

METBD 050

LOOK-UP Functions
The LOOK-UP functions are used to “look-up” a value from a table in a worksheet. The four functions most commonly used to find information from a table are VLOOKUP, HLOOKUP, MATCH and INDEX.

VLOOKUP searches for a value in the left-most column of a table of data and returns a value in the same row from a different column in the table. The syntax for the function is
VLOOKUP(Lookup_value,Table_array,Col_index_num)

where:

Lookup_value - is the value to be looked up in the first column of the Table_array

Table_array - is the range of cells that contain the table

Col_index_num - is the column to pull the data from.
The function searches for the Lookup_value in the first column of the range specified in Table_array. It will find the exact value or the largest value that is smaller than the look-up_value. A value is returned from another column in the row found by the function. The Col_index_num specifies the column from which the value is selected. By default, the table must be sorted in an ascending order. Ascending order starts with the smallest value at the top with increasing values down the table (A to Z or 0 to 9).

Errors: #N/A the lookup_value is smaller than the smallest value in the first column.

#VALUE the Col_index_num is less than 1.

#REF the Col_index_num is greater than the number of columns in the table

EXAMPLE: Create a vlook-up table to return the head diameter and height for a socket head cap screw when the nominal size is specified.


HLOOKUP looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify. The syntax of this three argument function is:
HLOOKUP(Lookup_value,Table_array,Row_index_num)

where:

Lookup_value - is the value to be looked up in the first column of the Table_array

Table_array - is the range of cells that contain the table

Row_index_num - is the row to pull the data from.
The function searches for the Lookup_value in the first row of the range specified in Table_array. It will find the exact value or the largest value that is smaller than the look-up_value. A value is returned from another row in the column found by the function. The Row_index_num specifies the row from which the value is selected. By default, the table must be sorted in an ascending order. Ascending order starts with the smallest value at the left with increasing values towards the right side of the table (A to Z or 0 to 9).
Errors: #N/A the lookup_value is smaller than the smallest value in the first row.

#VALUE the Row_index_num is less than 1.

#REF the Row_index_num is greater than the number of rows in the table

EXAMPLE: Create an hlook-up table to return the head diameter and height for a socket head cap screw when the nominal size is specified.

Cell Formulas Used:
C5: =HLOOKUP(C3,C8:I10,2)

C6: =HLOOKUP(C3,C8:I10,3)

MATCH returns the relative position of an item in an array that matches a specified value in a specified order. The syntax of this three argument function is:
MATCH(lookup_value,lookup_array,match_type)

Where,

Lookup_value – is the value you use to find the value you want in the table.

Lookup_array – the range of cells to search in.

Match_type – is the number –1, 0, or 1.

• 
  If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
  
• 
  If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
  
• 
  If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
  
• 
  If match_type is omitted, it is assumed to be 1.
  

Errors: #N/A the lookup range isn’t in proper order, or the lookup_value isn’t in the lookup_array and the match_type is set to 0.

The value displayed is the position within the lookup_array that contains the value looked up.

Ascending order

Any order

Descending order

INDEX returns a value or the reference to a value from within a table or range. The syntax for the three argument function is:
INDEX(array,row_num,column_num)

Where,

Array – the range of cells from which the data is pulled.

Row_num – the row number from which the data is pulled.

Column_num – the column number from which the data is pulled.


INDEX Example:

ROW value is larger than the number of rows in the ARRAY. The same error is produced if the number of columns is exceeded.


EXAMPLE: Write a worksheet selects the proper W12 beam from a table given the minimum safe section modulus.


CELL FORMULAS:

B4: =MATCH(B2,C10:C21,-1)

B5: =INDEX(B10:B21,B4,1)
B7: see below
The B4 function finds the number from B2 in the array C10:C21, or the next biggest number. The function returns the row number in B4. It’s 9 in the example.
The B5 function returns the value contained in the B4 row and column 1 of the range B10:B21. In this case, it is the 22.

B7: ="USE: W12 x " & INDEX(B10:B21,B4,1) & ", Sx = " & INDEX(C10:C21,B4,1) & “ cu. in."

E. R. Evans, Jr. - 9/24/01, Revised 11/6/05 Page of 2

METBD 050 – Lookup Functions.doc