EXCEL Formulas to be use in VLOOKUP to get multiple match in output

VLOOKUP

Excel VLOOKUP function to look up data in a table that  arranged vertically. Output of VLOOKUP will provide exact and approximate match as per input in formula. Lookup values to be available in the first column of the table to run the VLOOKUP.

Syntax

=VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])

Arguments 

lookup_value -  value to look from table and must be available in first column of                     table.

table_array – Selection of table.

column_index_num –No of colume which output is require.

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

COUNTIF

Excel COUNTIF function provides count of cells that meets the given criteria. Criteria supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Syntax 

=COUNTIF (range, criteria)

Arguments 

range - The range of cells to count.

criteria - The criteria that controls which cells should be counted.

ROW

Excel ROW function will provide row number where the formula is written

Syntax 

=ROW ([reference])

Arguments 

reference - [optional] A reference to a cell or range of cells.

IFERROR

Excel IFERROR formula will provide custom output when error is generated and return standard result when no error in input value/formula.

Syntax 

=IFERROR (value, value_if_error)

Arguments 

value - The value, reference, or formula.

value_if_error – input to get when error is generated

Practice file download

Excel text function || FIND, SEARCH, REPLACE, SUBSTITUTE

FIND

Excel FIND function will provide the position(As a number) of one text string within another text string. FIND is a case-sensitive. Reading is from left to right.

Syntex

                =FIND (find_text, within_text, [start_num])

Arguments 

                Find_text - The text to find.

                Within_text –The input text to search.

                [Start_num]- [optional] The starting position in the text to search. defaults to 1.

SEARCH                                               

Excel SEARCH function will provide the position(As a number) of one text string within another text string. SEARCH is not a case-sensitive. Reading is from left to right.

Syntex

                =SEARCH (find_text, within_text, [start_num])

Arguments 

                Find_text - The text to find.

                Within_text –The input text to search.

                [Start_num] - [optional] The starting position in the text to search. defaults to 1.

REPLACE

Excel REPLACE function will replace a part of text string with different text string. REPLACE is not a case-sensitive

Syntex

                =REPLACE (old_text, start_num, num_chars, new_text)

Arguments 

old_text – selection of text which require replacement.

start_num - The starting location in the text from where replacement require.

num_chars - The number of characters to be replace.

new_text - text to replace old_text with.

SUBSTITUTE

Excel SUBSTITUTE function will replace existing text with new text string in given text. SUBTITUTE function is a case-sensitive.

Syntex

=SUBSTITUTE (text, old_text, new_text, [instance])

Arguments 

text - The text to change.

old_text - The text to replace.

new_text - The text to replace with new text

[instance] - [optional] The instance to replace. If not supplied, all occurrence are replaced. i.e. if 1 is provided then first occurrence is replaced, remaining occurrence  will not change, if 2 is written then only second occurrence is replaced remaining occurrence will not change.

Practice file download

Excel text function || TRIM, PROPER, UPPER, LOWER, LEN, LEFT, RIGHT, MID, CONCATENATE

TRIM

Excel TRIM function will remove all unnecessary space in given text. TRIM function can only  remove regular space(ASCII CODE 32).

    Syntex

    =TRIM(text)

    Arguments 

    Text – word, sentence, paragraph etc

For more details on TRIM function

PROPER

Excel PROPER function will covert first character of given word to capital letter and remaining to small letter and if sentence like structure is given then it will convert all words first character capital and remaining to small.

    Syntex

    =PROPER(text)                                                          

    Arguments 

    Text – word, sentence, paragraph etc

UPPER

Excel UPPER function will convert given text to capital letters.

    Syntax 

    =UPPER(text)

    Arguments 

    Text – word, sentence, paragraph etc

LOWER

Excel LOWER function will convert given text to small letters.

    Syntax 

    =LOWER(text)

    Arguments 

    Text – word, sentence, paragraph etc

LEN

Excel LEN function will provide you a number of character in given text including space.

    Syntax 

    =LEN(text)

    Arguments 

    Text – word, sentence, paragraph etc

LEFT

Excel LEFT function will provide the character from left side of given text. And if the second optional argument is not justified then function give you a left first character from given text.

    Syntax 

    =LEFT(text,[num_chars])

    Arguments 

    Text – word, sentence, paragraph etc

    Optional argument[num_chars] – number of character need to specify how much                character we need in output from left side

RIGHT

Excel RIGHT function will provide the character from right side of given text. And if the second optional argument is not justified then function give you a right single character from given text.

    Syntax 

    =RIGHT(text,[num_chars])

    Arguments 

    Text – word, sentence, paragraph etc.

    Optional argument[num_chars] – number of character need to specify how much                   character we need in output from right side

MID

Excel MID function will provide middle characters from given text.

     Syntax 

    =MID(text,start_num,num_chars)

    Arguments 

    Text – word, sentence, paragraph etc

    Start_num – location of character in number of which we need as a first letter of our             final text.

    Num_chars – Number of character up to which we need text.

CONCATENATE

Excel CONCATENATE is use to join text from two different cells

    Syntax 

    =CONCATENATE(text1,[text2],[text3]...)

    Arguments 

    Text1 – word, sentence, paragraph etc.

    [text2],[text3]...- word, sentence, paragraph etc.

Practice file download

Statistical functions How to use COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS in excel

COUNT

Excel COUNT function provides count of values that are numbers. Numbers include negative, positive, times, dates, fractions, percentages, and formulas that return numbers. 

    Syntex

    =COUNT (value1, [value2], ...)

    Arguments 

    value1 - An item, cell reference, or range.

    value2 - [optional] An item, cell reference, or range.

 

COUNTA

Excel COUNTA function provides count cells that contain number, text, formula, errors and non-empty cells. COUNTS does not count blank cells.   

    Syntex

    =COUNTA (value1, [value2], ...)                         

    Arguments 

    value1 - An item, cell reference, or range.

    value2 - [optional] An item, cell reference, or range.

 

COUNTBLANK

Excel COUNTBLANK function provides count of cell that is blank

    Syntax 

    =COUNTBLANK (range)

    Arguments 

    range– Selection of input

 

COUNTIF

COUNTIF function provides count of cells that meets the given criteria. Criteria supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

    Syntax 

    =COUNTIF (range, criteria)

    Arguments 

    range - The range of cells to count.

    criteria - The criteria that controls which cells should be counted.

 

COUNTIFS

COUNTIFS function provide count of cells that meets the one or more criteria.

    Syntax 

    =COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

    Arguments 

    range1 - The first range

    criteria1 - The criteria to use on range1.

    range2 - [optional] The second range.

    criteria2 - [optional] The criteria to use on range2.

COUNTIFS will provide only those counts of cells that met the all criteria in given range.

 Practice file download 

How to remove unwanted space in excel || TRIM function

Excel TRIM function is use to remove un-necessary space or “NORMALIZE” all spacing from the given text. This TRIM function has only one argument is TEXT.

Syntex.

=TRIM(text)

TRIM function keep only keep single space between two words/letters and remove all leading and trailing spaces.

Limitation of TRIM function

·         TRIM only removes the space with ASCII code character (32) from text.

·         Many time texts may contain with non-breaking space which have ASCII code character (160) that may appear from web page and other document, those non-breaking space cannot remove by TRIM function.

To remove those type of non-breaking space we need to use SUBSTITUTE function with CHAR function in excel  to remove those un-necessary space.

 How??

First it needs to replace by all non-breaking space to regular space in given text by using SUBSTITUTE and CHAR function

Syntax of SUBSTITUTE function

=SUBSTITUTE(text,old_text,new_text,[instance_num])

                text = text which need to TRIM

                old_text = CHAR(160)

                new_text = CHAR(32)

                Optional argument [instance_num] ,which need not to specify

Where CHAR function will represent character ASCII code 160(non-breaking space) and character ASCII code 32(regular space) in the selected text.

In output of SUBSTITUE function, replacement of all non-breaking space to regular space.

last we can use TRIM formula as mention below to get the final text without un-necessary space.

=TRIM(SUBSTITUTE(text,old_text,new_text))

Practice file download

How to use RATE formula in excel

RATE formula used to get the rate of interest use to calculate the EMI amount from the 

main input i.e. Loan amount, EMI amount and Number of payments.

    Syntex

    =RATE (nper, pmt, pv, [fv], [type], [guess])

There were total six arguments in RATE formula, out ot which 3 were main arguements and 3 is optional arguments

Main Argument,

    nper - No of payments

    pmt  - Payments amount/EMI

    pv     - Present value/loan amount

Optional arguments,

    fv- Future value; by default it will be 0

    type - 0 or 1, 0 means for payment at the end of period and 1 means payment at the start of period; by default it will be 0

    guess - default to 10% 

Final answer of RATE formula is in %.

   Practice file download