Blog

Engineering, Lookup & Text Functions

Engineering Function

CONVERT()

Converts a number from one measurement system to another. CONVERT can translate a table of distances in miles to a table of distances in kilometers.

The Syntax of the convert function is: =CONVERT(number,from_unit,to_unit)

CONVERT Function

Example:

Here in the example, we are converting 1 Inch into 1 centimeter and 1 foot and 1 yard into a meter. So in cell D2 the function is written like this: =CONVERT(A2,B2,C2) and copied in the respective cells. The result can be seen in the figure above.

Some common from_unit/to_unit

Actual Unit Unit used in function
Gram “g”
Meter “m”
Inch “in”
Foot “ft”
Yard “yd”
Year “yr”
Day “day”
Hour “hr”
Minute “mn”
Second “sec”
Celcius “C” / ‘’cel”
Fahrenheit “D” / “fah”
Liter “I” / “It”
Kilo “k”
Centi “c”
Mill “m”

Lookup & Reference functions

These functions are useful when the user has to extract a single set of data from a long list of the data sets.

VLOOKUP ()

The VLOOKUP function looks up the value in the first column of the lookup table and returns the corresponding value in a specified table column. The lookup table is arranged vertically (which explains the V in the function name). The syntax for the VLOOKUP function is

The VLOOKUP function’s arguments are as follows:

  • Lookup_value: The value to be looked up in the first column of the lookup table.
  • Table Array: The range that contains the Lookup table.
  • Col_index_num: The column number within the table from which the matching value is returned.
  • Range_lookup: Optional. If TRUE or omitted, the nearest match is returned. (If an exact match is not found, the next largest value that is less than look_value is returned.) If FALSE, VLOOKUP will search for an exact match. If VLOOKUP can’t find an exact match, the function returns #N/A.

Tips: If the range_lookup argument is FALSE, the first column of the lookup table need not be in ascending order. If an exact match is not found, the function returns #N/A

If the lookup_value argument is text, it can include wildcard characters * and?

VLOOKUP Function

Example:

The figure above shows a simple worksheet that uses two lookup formulas. This worksheet contains a table of employee data, beginning in row 7. This range is named EmpData. When you enter a last name into cell D10, lookup formulas in D3:F7 to retrieve the matching information from the table. The following lookup formulas use the VLOOKUP function:

E10 -> =VLOOKUP(D10,D3:F7,2,FALSE)

F10 -> =VLOOKUP(D10,D3:F7,3,FALSE)

HLOOKUP ()

The HLOOKUP () function works just like the VLOOKUP function except that the lookup table is arranged horizontally instead of vertically. The HLOOKUP function looks up the value in the first row of the lookup table and returns the corresponding value in a specified table row.

The Syntax for the HLOOKUP function is

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

The HLOOKUP function’s arguments are as follows

  • Lookup_value: The value to be looked up in the first row of the lookup table.
  • Table_Array: The range that contains the lookup table.
  • Row_Index_num: The row number within the table from which the matching value is returned.
  • Range_Lookup: Optional. If TRUE or omitted, an approximate match is returned. (If an exact match is not found, the next target value less than lookup_value is returned). If FALSE, VLOOKUP will search for an exact match. If VLOOKUP can’t find an exact match, the function will turn #N/A
HLOOKUP Function

Text Functions

Text ()

This function converts a value to text in a specific format.

The syntax of this function is =Text(Value, Format). Value is the value to convert and Format is the formatting specification in which the value would be converted.

Let us take an example. You have a worksheet that has a cell that has to show the current day of the week in the following manner: ‘Today is <the day>’

This is a problem as we are going to combine a text and a date. To combine two different data type and display it as text we can use the text function like this way: =“Today is “ & text(today(), “DDDD”)

Exact ()

Exact functions compare two text strings and return TRUE if they are exactly the same, FALS otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document. The syntax of the function is :

=EXCAT(text1, text2)

Example:- Cell A1 contains text1 “Sachin” and text2 “SACHIN”, Return False

  If text1 “Sachin” and text2 “Sachin”, Return True.

Upper ()

This function converts the string of a cell or cells into capital letters. The Syntax of the function is =UPPER(<Text>)

Text is the string or cell references you want to convert into uppercase

Example:

  •   =UPPER(“ENGLISH”) Returns English into the current cell.
  •   =UPPER (“English”)     Returns English into the current cell.
  •   =UPPER (B2)               Converts the strings of cell B2 into upper case in the current cell.  

Lower ()

This function converts the string of a cell or cells into Small letters. The Syntax of the function is =LOWER(<Text>)

Text argument asks for a string or cell references for conversion

Example:

  •  =LOWER(“ENGLISH”) Returns English into the current cell.
  •  =LOWER (“English”)   Returns English into the current cell.
  •  =LOWER (B2:B8)         Converts the strings of the cell B2:B8 into lower case.  

Proper ()

The proper function converts the string of a cell or cells into the proper case, which means the first letters of the string will be in the upper case and the remaining part will be in the lower case. The general format for the function is =PROPER(<Text>)

Example:

  •   =PROPER(“ENGLISH”) Returns English into the current cell.
  •   =PROPER (“English”)   Returns English into the current cell.
  •   =PROPER (B2:B8)         Converts the strings of the cell B2:B8 into the proper case within the currently selected area.

CONCANATE ()

This function is joined text1, and text2, which are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references. You can use the “&” operator can be used instead of CONCATENATE to join text items.

Example: Cell A1 contains the text “Vishal” and Cell A21 contains the text “Shah”. Now if you want to join the text into a single cell like B10.

  •   =concatenate(A1,A2)
  •   =concatenate (A1&A2)
  •   =concatenate (A1,””,A2)
  •   =concatenate (A1&””&A2)

Exact()

Exact functions compare two text strings and return TRUE if they are exactly the same, and FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document. The syntax of the function is:

=EXACT(text1,text2)

Example: – Cell A1 contains text1 “Debjit” and text2 “DEBJIT”, Return False.

If text1 “Debjit” and text2 “Debjit”, Return True.

Tags: ,