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.