Lookup Function

This function is useful when we want to get the approximate match of any value.

Summary 

The Excel LOOKUP function performs an approximate match lookup in a one-column or one-row range, and returns the corresponding value from another one-column or one-row range. LOOKUP's default behavior makes it useful for solving certain problems in Excel.

Purpose 

Look up a value in a one-column range.

Return value 

A value in the result vector.

Syntax 

=LOOKUP (lookup_value, lookup_vector, [result_vector])

Arguments 

lookup_value - The value to search for.

lookup_vector - The one-row, or one-column range to search.

result_vector - [optional] The one-row, or one-column range of results.

Usage notes 

Note: This page describes the vector form of the LOOKUP function. In this case, a vector refers to a one-column or one-row range.

Use the LOOKUP function to look up a value in a one-column or one-row range, and retrieve a value from the same position in another one-column or one-row range. The lookup function has two forms, vector and array. This article describes the vector form.

LOOKUP has default behaviors that make it useful when solving certain problems (i.e. retrieve approximate matched value instead of position, find the value in the last non-empty cell in a row or column, etc.). LOOKUP assumes that values in lookup_vector are sorted in ascending order and always performs an approximate match. When LOOKUP can't find a match, it will match the next smallest value.

LOOKUP assumes that lookup_vector is sorted in ascending order.

When lookup-value can't be found, LOOKUP will match the next smallest value.

When lookup_value is greater than all values in lookup_vector, LOOKUP matches the last value.

When lookup_value is less than all (i.e. the first) value in lookup_vector, LOOKUP returns #N/A.

result_vector must be the same size as lookup_vector.

LOOKUP is not case-sensitive

INDIRECT Function in Excel with Few Examples

This function is has a good usage in dynamic range for summing up, for average and so on.

There is no need to directly select the cells range we can only give the cell reference which shows the numbers or cell reference.

Summary 

The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference.

Purpose 

Create a reference from text.

Return value 

A valid worksheet reference.

Syntax 

=INDIRECT (ref_text, [a1])

Arguments 

ref_text - A reference supplied as text.

a1 - [optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.

The INDIRECT function in Excel returns the reference specified by a text string.

1.       For example, the INDIRECT function below reduces to =INDIRECT("B2"), =B2, 5

This is very useful function for dynamic range. Below you can find the result without using the INDIRECT function.

2.       For example, the function below reduces to =SUM(INDIRECT("A3:A5")), =SUM(A3:A5), 60

3.       For example, the function below reduces to ==AVERAGE(INDIRECT("A"&B1&":A"&B2)), 20

Formulatext Function in Excel

Syntax

FORMULATEXT(reference)

The FORMULATEXT function syntax has the following arguments.

Reference    Required. A reference to a cell or range of cells.

Choose Function in Excel

Summary

The Excel CHOOSE function returns a value from a list using a given position or index. For example, CHOOSE(1,"Pass","Fail","Grace") returns "Pass", since blue is the 1st value listed after the index number. The values provided to CHOOSE can include references.

Purpose

Get a value from a list based on position

 

Return value

The value at the given position.

Syntax

=CHOOSE (index_num, value1, [value2], ...)

 

Arguments

index_num - The value to choose. A number between 1 and 254.

value1 - The first value from which to choose.

value2 - [optional] The second value from which to choose.

VLOOKUP Function from Right to Left

Excel's VLOOKUP function is used to find and return information from a table of data based on a lookup value that you choose.

Normally, VLOOKUP requires the lookup value to be in the left-most column of the table of data, and the function returns another field of data located in the same row to the right of this value.

By combining VLOOKUP with the CHOOSE function

The steps detailed below create the left lookup formula seen in the image above.

The formula

The job of the CHOOSE function in the formula is to trick VLOOKUP into believing that column 3 is actually column 1. As a result, the Employee ID can be used as the lookup value to find the name of the department name.

Enter the following headings into the cells indicated:

Column “C” shows the Employee ID

Column “B” Shows the Department

Excel VLOOKUP Function

VLOOKUP is designed to retrieve data in a table organized into vertical rows, where each row represents a new record. The "V" in VLOOKUP stands for vertical and “H” in HLOOKUP stands for horizontal.

Purpose

Lookup a value in a table by matching on the first column

Return value

The matched value from a table.

Syntax

=VLOOKUP (value, table, col_index, [range_lookup])

Arguments

value - The value to look for in the first column of a table.

table - The table from which to retrieve a value.

col_index - The column in the table from which to retrieve a value.

range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.

Let's learn this function with below example.

We need unique values for VLOOKUP function. As in the below example there is unique values are employee id’s.

We have other sheet where we want department data related to employee id.

Function we used above is as shown below.

=Vlookup(B2,Sheet1!B1:C6,2,0)

In the function 2 means we have taken values from 2^nd columns as shown below.

We have taken selection from unique cell value that is B columns and we take the value from 2^nd column that’s why we have taken 2^nd column in the VLOOKUP function.