Excel Data Analysis Lookup Functions - Excel Data Analysis

What is Excel Data Analysis Lookup Functions?

You can use Excel features to −

• Find values in a range of data - VLOOKUP and HLOOKUP
• Obtain a value or the reference to a value from inside a table or range - INDEX
• Obtain the relative function of a specified item in a number of cells - match

You can also combine those features to get the specified results based on the inputs you have.

Using VLOOKUP Function

The syntax of the VLOOKUP function is

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

• Lookup value − is the value you need to look up. Lookup_value may be a value or a reference to a cell. Lookup_value should be in the first column of the range of cells you specify in table_array
• Table array − is the range of cells in which the VLOOKUP will look for the lookup_value and the return value. table_array must contain
  • The lookup value in the first column, and
  • The return value you want to find

Note − the first column containing the lookup_value can either be sorted in ascending order or not. but, the result can be based at the order of this column.

• col_index_num − is the column number within the table_array that includes the go back value. The numbers start with 1 for the left-most column of table-array
• Range lookup − is an optional logical value that specifies whether you need VLOOKUP to find an precise match or an approximate match. range_lookup can be
  • Omitted, in which case it is assumed to be genuine and VLOOKUP tries to locate an approximate match
  • True, in which case VLOOKUP tries to locate an approximate match. In different words, if an precise match is not found, the next largest value that is less than lookup_value is returned
  • False, in which case VLOOKUP tries to find an exact match
  • 1, in which case it is assumed to be proper and VLOOKUP tries to find an approximate match
  • 0, in which case it is assumed to be false and VLOOKUP tries to find an exact match

Note − If range lookup is omitted or true or 1, VLOOKUP works correctly only while the first column in table_array is sorted in ascending order. otherwise, it may result in wrong values. In such a case, use false for range lookup.

Using VLOOKUP function with Range_Lookup True

Consider a list of student marks. you can obtain the corresponding grades with VLOOKUP from an array containing the marks intervals and pass category.

Table array –

Note that the first column marks based on which the grades are received is sorted in ascending order. Therefore, using true for range_lookup argument you can get approximate match that is what is needed.

Name this array as Grades.

It is a good practice to name arrays on this way so that you need not consider the cell levels. Now, you are ready to look up the grade for the list of marks you have as follows –

As you can observe,

• col_index_num − shows the column of the return value in table_array is 2
• The range_lookup is true
  • The first column containing the lookup value inside the table_array grades is in ascending order. hence, the results can be correct.
  • You can get the return value for approximate matches also. i.e. VLOOKUP computes as follows –

You will get the following results –

Using VLOOKUP function with Range_Lookup False

Consider a list of products containing the Product identity and price for every of the products. The product identity and price will be added to the end of the list whenever a new product is released. this would mean that the product IDs need not be in ascending order. The product list might be as shown below −

Table array –

Name this array as Product Info.

You can obtain the price of a product given the product id with the VLOOKUP feature as the product id is in the first column. The price is in column 3 and hence col_index_ num have to be 3.

• Use VLOOKUP function with range_lookup as true
• Use VLOOKUP feature with range_lookup as false

The correct answer is from the Product Info array is 171.65. You can check the results.

You observe that you got −

• The correct result while range_lookup is false, and
• A wrong result when range_lookup is true.

This is because, the first column inside the Product Info array is not sorted in ascending order. hence, remember to use false whenever the data is not sorted.

Using HLOOKUP Function

You can use HLOOKUP function if the data is in rows rather than columns.

Example

Let us take the example of product data. Suppose the array looks as follows –

• Name this Array Product Range. You can find the price of a product given the product ID with HLOOKUP function.

The Syntax of HLOOKUP function is

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Where

• Lookup value − is the value to be located in the first row of the table
• Table array − is a table of data wherein data is looked up
• Row_index_num − is the row number in table_array from which the matching value can be back
• Range lookup − is a logical price that specifies whether you want HLOOKUP to find an precise match or an approximate in shape
• Range lookup can be
  • Omitted, in which case it is assumed to be true and HLOOKUP tries to find an approximate match
  • True, in which case HLOOKUP tries to locate an approximate match. In other words, if an precise match is not found, the following largest value that is less than lookup_value is returned
  • False, in which case HLOOKUP tries to locate an exact match
  • 1, in which case it is assumed to be true and HLOOKUP tries to find an approximate match
  • 0, in which case it is assumed to be false and HLOOKUP tries to locate an exact match

Note − If range_lookup is not noted or true or 1, HLOOKUP works successfully only when the first column in table_array is sorted in ascending order. otherwise, it may result in incorrect values. In such a case, use false for range_lookup.

Using HLOOKUP function with Range_Lookup False

You could obtain the price of a product given the product id with the HLOOKUP function as the product id is in the first row. The price is in row 3 and hence row_index_ num need to be 3.

• Use HLOOKUP function with range_lookup as true.
• Use HLOOKUP function with range_lookup as false.

The correct answer from the ProductRange array is 171.65. You can check the results.

You look at that as in the case of VLOOKUP, you got

• The correct end result when range_lookup is false, and
• A wrong result when range_lookup is true.

This is because the first row in the ProductRange array is not sorted in ascending order. hence, remember to use false whenever the data is not sorted.

Using HLOOKUP function with Range_Lookup True

Consider the example of student marks used in VLOOKUP. Suppose you have the data in rows instead of columns as shown in the table given below –

table array –

Name this array as Grades Range.

Note that the first row marks based on which the grades are received is sorted in ascending order. hence, using HLOOKUP with true for range_lookup argument, you can get the Grades with approximate match and that is what is required.

As you can observe,

• row_index_num − shows the column of the return value in table_array is 2
• the range_lookup is true
  • The first column containing the lookup value in the table_array Grades is in ascending order. hence, the results will be correct.

  • You can get the return value for approximate matches also. i.e. HLOOKUP computes as follows –

    Marks	< 35	>= 35 and < 50	>= 50 and < 60	>=60 and < 75	>= 75
    Pass Category	Fail	Third Class	Second Class	First Class	First Class with Distinction

You will get the following results –

Using INDEX Function

When you have an array of data, you can retrieve a value in the array by specifying the row number and column number of that value in the array.

Consider the following sales information, wherein you find the sales in each of the North, South, East and West regions by the salespersons who are listed.

• Name the array as SalesData.

Using INDEX Function, you can find −

• The Sales of any of the Salespersons in a certain Region.
• Total Sales in a Region by all the Salespersons.
• Total Sales by a Salesperson in all the Regions.

You will get the following results –

Suppose you do no longer know the row numbers for the salespersons and column numbers for the regions. Then, you need to locate the row number and column number first before you retrieve the value with the index function.

You can do it with the match function as explained within the next section.

Using Match Function

If you want the position of an item in a selection, you can use the match function. you can combine match and INDEX functions as follows –

You will get the following results –