String1,strin text1, text2



Yüklə 137,16 Kb.
tarix26.12.2017
ölçüsü137,16 Kb.
#36036

CONCATENATE Function:

Joins two or more text strings into one text string.



Syntax:

CONCATENATE (string1,string2,...)

Text1, text2, ...   are 2 to 255 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.

Remarks:

You can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join text items. For example, =A1&B1 returns the same value as =CONCATENATE(A2,B2) as shown below.





Eg:

The usage of the Concatenate function can be understand by the following screenshots.



Concatenation of two strings:



Concatenation of strings&numbers:



How to insert CONCATENATE formula by Menu options is Explained in the following video:

Double click the following item to play.

LEFT Function:

LEFT returns the first character or characters from the left in a text string or number, based on the number of characters you specify.



  • LEFT always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.

Syntax:

=LEFT(text, num of chars)

Text   is the text string that contains the characters you want to extract.

Num of chars   specifies the number of characters you want LEFT to extract.

  • Num of chars must be greater than or equal to zero.

  • If Num of chars is greater than the length of text, LEFT returns all of text.

  • If Num of chars is omitted, it is assumed to be 1 as shown below the screen shot.



Example:

An examples of LEFT Function is explained in the following screen shots.



RIGHT Function:

RIGHT Function is similar to LEFT Function returns the first character or characters from right side in a text string or number, based on the number of characters you specify.

  • RIGHT always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.

Syntax:

=RIGHT(text, num of chars)

Text   is the text string that contains the characters you want to extract.

Num of chars   specifies the number of characters you want LEFT to extract.

  • Num of chars must be greater than or equal to zero.

  • If Num of chars is greater than the length of text, RIGHT returns all of text.

  • If Num of chars is omitted, it is assumed to be 1 as shown below the screen shot.

LOOKUP Function:

Returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: the vector form and the array form.





Vector Form:

A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. Use this form of the LOOKUP function when you want to specify the range that contains the values that you want to MATCH. The other form of LOOKUP automatically looks in the first column or row.



LOOKUP(LookupValue,lookupVector,ResultVector)

LookupValue  A value that LOOKUP searches for in the first vector. LookupValue can be a number, text, a logical value, or a name or reference that refers to a value.

LookupVector  A range that contains only one row or one column. The values in LookupVector can be text, numbers, or logical values.

note:  The values in LookupVector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

ResultVector  A range that contains only one row or column. It must be the same size as lookup_vector.

Remarks:

  • If LOOKUP can't find the LookupValue, it MATCHes the largest value in LookupVector that is less than or equal to LookupValue.

  • If LookupValue is smaller than the smallest value in LookupVector, LOOKUP gives the #N/A error value.

Example:





Array Form:

The array form of LOOKUP looks in the first row or column of an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) for the specified value and returns a value from the same position in the last row or column of the array. Use this form of LOOKUP when the values that you want to MATCH are in the first row or column of the array. Use the other form of LOOKUP when you want to specify the location of the column or row.



Tip  In general, it's best to use the HLOOKUP or VLOOKUP function instead of the array form of LOOKUP. This form of LOOKUP is provided for compatibility with other spreadsheet programs.

LOOKUP(LookupValue,Array)

LookupValue is a value that LOOKUP searches for in an array.LookupValuecan be a number, text, a logical value, or a name or reference that refers to a value.

If LOOKUP can't find the LookupValue, it uses the largest value in the array that is less than or equal to LookupValue.

IfLookupValueis smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

Array  is a range of cells that contains text, numbers, or logical values that you want to compare with LookupValue.

The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches forLookupValuein the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.

If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches forLookupValuein the first row.

If array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.

With HLOOKUP and VLOOKUP, you can index down or across, but LOOKUP always selects the last value in the row or column.

note:The values in array must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.



How to create emails for employees using excel formula?

You can create the email ids in excel using LEFT or RIGHT and CONCATENATE functions.

Eg: Suppose in your organization/company if you want to generate the email ids of your employees in EXCEL sheet by using the first letter of the NAME and full SURNAME , that can be done as follows:

Explain:

Suppose the cell ‘A1’ has the employee name “jacksmith” and cell ‘B2’ has the surname then write the if you drop the following formula in Desire cell you will get the following resut.

Here:

>> LEFT(A2,1) functions takes the 1 character from the left of value of ‘A2’.

>>CONCATENATE(LEFT(A2,1),B2,”@gmail.com”) ,here CONCATENATE is a function which concatenates/jons the two or more cell values and or user defined values (here the string ‘@gmail.com’ is joins with cell values A2,B2) ,numbers in to one cell values.

>> The stringt hat you want to add to the cell value should be in double quotes as shown below.

NAME

SURNAME

FORMULA FOR TO CREATE EMAIL

Result

Jacksmith

hurrey

=CONCATENATE(LEFT(A2,1),B2,"@gmail.com")

Jhurrey@gmail.com 

Jacksmith

hurrey

=CONCATENATE(LEFT(B3,1),A3,"@xyz.in")

hJacksmith@xyz.in 



Why Drop Down Buttons used and How to create them?

Why they need:

Drop down button generally used to simplyfy your process . As explained below.

For example, if you want to assign Department Name for each employee , without typing you can just select the Department Name from the drop downlist available for each employee.

Suppose your company has four departmens namely HR,FINANCE,PRODUCTION,MARKETING,You these 4 as list to show in the dropdown button as shown below.





How to Create:

To create simple drop down butoon use the following steps.

  • First selet desired cell/coloumn where you want to put dropdown list.

  • Next got to Data > Data Validation



  • Next choose Settings >Allow>list from the Data Valiadtation dialogue box.



  • Next type the dropdown list items and clik ‘OK’.



Now a dropdown list for each cell in the selected coloumn has been created.

How to deine a LIST name and use as the Dropdown ?

Youcan define a name for the list of values and that LIST name can used as source for the Dropdown button.

We can also use this LIST name in different formulae instead of mentioning the LIST of RANGE of values .

How to define LIST name:

To define a LIST name for a long list of values use the following steps.



  • First define list of values in a desired column and select all values give a list name in NAME BOX(which located in the left most corner and top of the column ‘A’. , as shown below.


We can use the above list name ‘MONTHS’ as the source of list Dropdown list as follows.





Result:

Now we can see the dropdown button by using the LIST name is as follows



What is IF, why and how IF is Used in Excel?

IF:

IF is a conditional function, based on the given condition or logical test it written either TRUE statement(if the condition is true) or FALSE statement(if the condition fails).



Syntax:

=IF(Condition, TrueValue,FalsValue)

Where:

  • Condition is what you want to test (Eg: a=b, a=b,…).

  • TrueValue refers to true statement.

  • FalsValue refers to true statement

Eg:

Suppose cell ‘A1’ has a value 10 and cell ‘B1’ has a value 12 . Now if you want test whether A1 is greater than B1 or not., and you want the True statement as ‘Greater’ and False statement as ‘lesser’, then you have to write the following formula in Cell C1 as shown below



=IF(A1>B1,”Greater”,”lesser”)

Here True and False statement should be in double quotes because they are strings.



Note: In the above Formula in the screenshot ,if the condition fails it will written the result as “lesser”.



What is COUNTIF, why and how COUNTIF is used in Excel?

CountIF is a function which used to find the count of a particular value (how many times it repeated in the given data) in the given array or range of data.

Syntax:

=CountIF(LookupArray,LookupValue)

Here:

  • LookupArray is a list or range of data where you want to find the count of a LookupValue.

  • LookupValue is the value that you want to count.

Eg:

Suppose column A, column B has some values, in that if you find the count of a particular value, you can do by the following way.





Remarks: If the value that you want to count is not existed in given range of data then its count is “0”.

MATCH Function:

hide allHide All

Returns the relative position of an item in an Array/Range of data that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.



Syntax :

MATCH(LookupValue,LookupArray,MatchType)

Here:

  • LookupValue is the value you use to find the value you want in a table or given range of data.

  • LookupValue is the value you want to MATCH in LookupArray.

For example, when you look up an Employee ID number in a list, you are using the Employee name as the lookupValue, but the ID number is the value you want.

  • LookupValue can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

LookupArray  is a contiguous range of cells containing possible lookup values. LookupArray must be an array or an array reference.

MatchType is the number -1, 0, or 1. MatchType specifies how Microsoft Excel matches LookupValue with values in LookupArray.

  • If MatchType is 1, MATCH finds the largest value that is less than or equal to LookupValue. LookupArray must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

  • If MatchType is 0, MATCH finds the first value that is exactly equal to LookupValue. LookupArray can be in any order.

  • If MatchType is -1, MATCH finds the smallest value that is greater than or equal to LookupValue. LookupArray must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

  • If MatchType is omitted, it is assumed to be 1.

Example :

For example, If you want to look up the position of an Employee ID(B2=143 which is LookupValue)then you can do by the following way.





Remarks:

  • MATCH returns the position of the matched value within LookupArray, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.

  • MATCH does not distinguish between uppercase and lowercase letters when matching text values.

  • If MATCH is unsuccessful in finding a MATCH, it returns the #N/A error value.

  • If MatchType is 0 and LookupValue is text, you can use the wildcard characters, question mark (?) and asterisk (*), in LookupValue. A question mark matches any single character; an asterisk MATCHes any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

CountIF Function:

How to find duplicates in particular column has a list of data?

We can find the duplicates in particular column using CountIF and IF functions combination.



Syntax:

=COUNTIF (Array, Value)

Here:

Array refers to the range of data where you want to find duplicates

Value refers to the value for which you test uniqueness.

Example:

Suppose Column A has a list of data , in that if you want find the duplicates ,you have to write a formula in Column B as shown below.





Match Function: How to find duplicates in particular column by comparing and matching with another column?

By using IF and MATCH functions combination, you can find out the duplicates in a column by comparing it with another column.



Syntax:

=IF(ISNA(MATCH(LookupValue, LookupArray, MatchType)), False, True)

Here:

  • ISNA refers to IS Not Available means if the LookupValue is not available in the LookupArray, then the if condition returns the False statement first else returns the True statement.

For More reference about IF, MATCH Functions, Please search in Search Box available at the top of this Blog.

Remark:

  • If you don’t use the ISNA in the formula then IF condition returns #N/A for False Statement.



Note:

If you have any ambiguity/if you want any another formula/method Please comment about it.

VLOOK UP:

VLOOKUP Function:

Vlookup Funciton searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.
Syntax :
VLOOKUP(lookupValue,TableArray,ColIndexNum,RangeLookup)
Where:
LookupValue : The value to search in the first column of the table array. LookupValue can be a value or a reference. If LookupValue is smaller than the smallest value in the first column of TableArray, VLOOKUP returns the #N/A error value.
TableArray  :Two or more columns of data. Used as a reference to a range or a range name. The values in the first column of TableArray are the values searched by lookupValue. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.
ColIndexNum : The column number in TableArray from which the matching value must be returned. A ColIndexNum of 1 returns the value in the first column in TableArray ; a ColIndexNum of 2 returns the value in the second column in TableArray , and so on. If ColIndexNum is:

Less than 1, VLOOKUP returns the #VALUE! error value.


If ColIndexNum is Greater than the number of columns in TableArray , VLOOKUP returns the #REF! error value.
RangeLookup : A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than LookupValue is returned.
The values in the first column of TableArray must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. For more information.
If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of TableArray do not need to be sorted. If there are two or more values in the first column of TableArray that match the lookupValue, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Remarks
Remarks :




  • When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. For more information, see CLEAN and TRIM Funcitons.




  • When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value. For more information, see Convert numbers stored as text to numbers.



  • If range_lookup is FALSE and lookup_value is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.


Examples :

Vlookup-Basic Model 1

Let us suppose there seven months as Jan ……June , Ten names as Name1…..Name10 .

Now if you want to lookup the value of a particualar name (say Name5) in the desired column(say Apr).Then you can find using simpl Vlookup Function in the following manner( explained in the following screen shots) .



Model 2-Vlookup with Match Function:

Let us suppose there seven months as Jan ……June , Ten names as Name1…..Name10 .

Now if you want to lookup the value of a particualar name (say Name5) and particualar month(say Apr) selected from the list of names and months availble.Then you find using Vlookup and Match Functions Combination in the following manner( explained in the following screen shots) .

Syntax:

=VLOOKP(B14,A2:G11,MATCH(C13,A1:G1,0),0)

Explain:

Here the above formula will loolup the cell ‘B14’ value in the table array ‘A2:G11’ and returns a value that matches to cell ‘C13’.









Model 2-Vlookup with Columns Function:

Syntax:

=VLOOKUP(B14,A2:G11,COLUMNS(A:G),0)

Explain:

Here the COLUMNS (A:G) function returns values form the column ‘G’ which matches to the lookup value B14(Name 7).

In the same way if you take COUMNS(A:D) it will return the value from the column ‘D’ which mathes to the lookup value.



LOOKUP a Value Based On Multiple Criteria Using INDEX and MATCH Functions:

By Using INDEX and Match Functions Combination we can lookup up a value in any direction of the table.



This Formula vs. VLOOKUP

The Vlookup Function only searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. .


.i.e., VLookup Function works in Left-Right direction only.

Example:

Let us suppose there are three Parent Companies (say 123, 143, 456) which have three different subsidiaries in three different countries (say India, China, America).

Suppose now if you want lookup a Subsidiary Name of a Particular Parent in the Particular Country we can find as in the following manner

Formula:

=INDEX (C2:C10, MATCH (1,(E2:E10=C13)*(D2:D10=D13),0),0)

Here:

>> C2:C10 is the range where you have the subsidiary names, from which you want extract/lookup, one that is in the index of a selected Parent Co. and Country.

>> E2:E10 is the range where you have the Parent companies names.

>> D2:D10 is the range where you have the Country names.

>> MATCH (1, (E2:E10=C13)*(D2:D10=D13), 0), is the Match Function which refers to row number of the Index Function. Here if the value in E2:E10 is equals/matches to ‘C13’ and(* refers to AND) D2:D10 equals/matches to ‘D13’ then value in the corresponding row of C2:C10 is the Lookup value.

Note:

The above formula is Array formula so that we should press ‘Ctrl+ Shift+ Enter’ to get the desired result otherwise we will get an error ‘#VALUE’.





COLUMN & COLUMNS Functions:

Syntaxes:

=COULUMN(ARRAY)

=COLUMNS(ARRAY)

Explain:

=COLUMN(A1:A10) returns 1

=COLUMN(A1:A10) returns 10 ,i.e.,the no.of columns .

Note: In the same ROW and ROWNS Functions are working.

INDEX FUNC TION:

The index function returns the index value that is the value located at the intersection of specified row and column



Syntax:

=INDEX(ARRAY,ROWNO,COLNO)



Here:

>> ARRAY is the range of data you specified.

>> ROW NO is the row number.

>>COLNO is the column number.



Example : Explained in the following Screenshot.



LEFT:

LEFT is text function returns the desired no.characters from the left of the cell value(either string or number).



Syntax:

=LEFT(VALUE,NO.OF CHARS)

Here:

>> VALUE refers to the cell value from which you want the specified n.of charaters from the left.

>> NO.OF CHARS refers to the no.of characters that you want from cell value.

Example : Explained in the sreen shots.

Here in the following example , the formula returns 3 characters from the left of value A2.







RIGHT:

RIGHT is text function returns the desired no.characters from the left of the cell value(either string or number).



Syntax:

=RIGHT(VALUE,NO.OF CHARS)

Here:

>> VALUE refers to the cell value from which you want the specified n.of charaters from the right.

>> NO.OF CHARS refers to the no.of characters that you want from cell value.

Example : Explained in the sreen shots.

Here in the following example , the formula returns 5 characters from right of the value A2.





MID Function:

Mid is a text function which returns/extracts the middle text of a string based on the starting position and no. of characters to extract.



Syntax:

=MID(TEXT, Starting Position, No. of Chars)

Here:

>> Text is the string from which you extract the middle text.

>> Starting position refers to the position from which the no. of characters has to extract.

>> No.of Characters to extract.



TRUNC Function:

TRUNC is numerical function which truncates a number to an integer by removing the decimal, fraction portion.



Syntax:

TRUNC (VALUE, NO.OF DIGITS)

Here:

>> VALUE which should be a number that you can trunk.

>>No. Of Digits refers to the size to truncate, if you don’t mention this then the function will truncate entire decimal portion.







LEN Function:

LEN function returns the length of the string/text.



Syntax:

=LEN(TEXT)





TRIM Function:

Trim function trims/removes all trailing, leading and additional spaces with in the text , but keeps one space between the words with in text.

Syntax:

TRIM(TEXT)

Example:

Explained in the screen shot






PRIMARY KEY IN EXCEL- CUSTOM VALIDATION:

A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table.


Suppose In the "EMPLOYEE TABLE" table below, the "EMP ID" column is the primary key, meaning that no two rows can have the same Employee ID. The Employee ID distinguishes two persons even if they have the same name.

You can declare EMP ID column as a primary key by the following steps:

>> First select entire empty column(a column with no input).

>> Next go to DATA > DATA VALIDATION

>> Next choose CUSTOM from Allow Drop down list and write the following formula in the Formula Box.

=COUNTIF($A$1:A1,A1)<=1

This means each cell beginning from A1 to entire range of A column allows only unique values as shown below.

Suppose of you try to enter duplicate values then you will get the following “error alert” message.





How to get only Date value from a cell having Date and Time?

A. Suppose cell C1 have a date and time but if you want only date in D1 then simply use the following formula.

=DATE(YEAR(C1),MONTH(C1),DAY(C1))

Example :



How to get only Time value from a cell having Date and Time?

A. Suppose cell C1 have a date and time but if you want only time in D1 then simply use the following formula.

=TIME(HOUR(C1),MINUTE(C1),SECOND(C1))

Example :

How to see the Formula in a cell itself without going for formula address bar?

A. In general if you wrote a formula in a cell say B1, then B1 shows the result of the formula and formula is shown in formula address bar as shown below.

But if you want see a formula in a cell itself you just keep one space before formula (before “ =” sign of formula in cell )as shown below.





How to Convert Text to Columns

Conversion of Text Columns refers to separation of simple cell content in to different columns.

We can done by using Text to Columns wizard as explained in following example.

Example:


Suppose column A having full names(Name and Surname) of the employees whose Name and Surnames are separated by a particular delimiter(such as “”,’;:._-) and if you want Name and Surname in different columns say B and C then you can do in the following manner.

>> First go to Data >> Text to Columns.

>>Next mark the Delimited Option and click Next> button as shown below.



>> Now mark all delimiter types such as Comma, Space, Semicolon, Tab and select Other if there is any other delimiter existed between the names and specify the other delimiter in the given other delimiter box. As shown below.



>> Now press Next> button.

>>Next select the Destination where you want the result.



>>Now Click Finish, then you will see the result as Name and Surname In different columns as shown below.



How to create a hyper link from Excel to a specific location in word document ?

If you want to create a hyperlink from your Excel to a particular context in some page of a word document , you have do the following things.

>> First open the word document

>>Next select desired text for which you have to create a book mark for hyper link as follows.



>> After defining the Bookmark Name save and close the word document.



Note: Bookmark name does not allow spaces ,numbers, special characters except underscore.

>>Now open the Excel sheet and select a cell where want insert the hyperlink.

>>Next select the file from the hyper link dialogue box to create link.

>>Now add the book mark name following by ‘#’ to the filename in the Adress bar shown below.

Eg: Sample.docx#mybookmark

>> In the Text to Display address bar you can give your desired name.



Now click ‘OK’ you will see a desired hyper link as shown below.





Wild Card Characters:

The Special characters such as *(Asterisk), & (Ampersand), ? (Question Mark) are known as wild card characters. The following table explains about the wildcard characters.





Example:

How these Wildcard Characters used in the functions are explained below.




SUMPRODUCT Function :


SUMPRODUCT Returns the sum of the products of corresponding ranges or arrays. The SUMPRODUCT is a multipurpose function used to find the count and sum based on multiple criteria.

Sumproduct is a Most Powerful Function that can did the Job of the functions like Sum, SumIf, Sumifs, Count, Countif and Countifs.



Syntax:

=SUMPRODUCT (Array1, Array2, Array3, ...)



How Many Ways Sumproduct Works:

In how many ways we can use sumprodcuct is explained in the following example:



Example:



Model1:

=Sumproduct(A3:A11*B3:B11) =200





How Does It Works:

(2*2)+(4*2)+(8*1)+(10*2)+……=200



Note: The *(Asterisk) is used in the formula to find the sum of the products of given array values

Model 2:

=Sumproduct(A3:A11+B3:B11) =121





How Does It Works:

(2+2)+(4+2)+(8+1)+(10+2)+……=121



Note: The +(Plus) is used in the formula to find the sum of the sums of given array values

Model 3:

=Sumproduct(A3:A11+B3:B11) =56





How Does It Works:

(2/2)+(4/2)+(8/1)+(10/2)+……=56



Note: The /(Division) is used in the formula to find the sum of the remainders of given array values.

Sumproduct To Find Count :

Example:



Declaration Method 1:



Declaration Method 2:



Here :

In Sumproduct Function we have taken three arrays and each array should satisfy a particular criteria, then returns Count.

We can use (*) or (--) symbols in between arrays.

In the above arrays there is no Numerical Data Array



Sumproduct To Find Sum:

Example:



Formula Usage to find Sum



Sumproduct Function Usage using Range Names:



Let us suppose if we define the List Names as follows :

SalExeID = A3:A26

Product = B3:B26

Jan = C3:C26

Feb = D3:D26

Mar = E3:E26

Sales = C3:E26

Then by using the above List Names in Sumproduct Function , We Can Find The Sum and Count As Follows.



Range/List Names Used in Sumproduct To Find Sum:





Lookup Based On Multiple Criteria Using Index- Match Functions

We can use Index-Match Functions combination to lookup a desired value in a range (list of data) based on multiple criteria.

By Using INDEX and Match Functions Combination we can lookup up a value in any direction of the table.

In this Formula you can specify any no. of criteria based on your requirement.



This Formula vs. VLOOKUP

The Vlookup Function only searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. .


.i.e., VLookup Function works in Left-Right direction only.

EXAMPLE:

Let us suppose there are three Parent Companies (say 123, 143, 456) which have three different subsidiaries in three different countries (say India, China, America).

Suppose now if you want lookup a Subsidiary Name of a Particular Parent in the Particular Country we can find as in the following manner.

FORMULA:

=INDEX (C2:C10, MATCH (1,(E2:E10=C13)*(D2:D10=D13),0),0)

Here:

>> C2:C10 is the range where you have the subsidiary names, from which you want extract/lookup, one that is in the index of a selected Parent Co. and Country.

>> E2:E10 is the range where you have the Parent Companies Names.

>> D2:D10 is the range where you have the Country Names.

>> MATCH (1, (E2:E10=C13)*(D2:D10=D13), 0), is the Match Function which refers to row number of the Index Function. Here if the value in E2:E10 is equals/matches to ‘C13’ and(* refers to AND) D2:D10 equals/matches to ‘D13’ then value in the corresponding row of C2:C10 is the Lookup value.

In this Formula you can specify any no. of criteria based on your requirement.



Note:

>>The above formula is an Array formula so that we should press ‘Ctrl+ Shift+ Enter’ to get the desired result otherwise we will get an error ‘#VALUE’.





FIND and SEARCH Functions:

Find Function & Search Function:

FIND Function is used locate one text string within another text string, and returns its position from search point.

Find Function is used for Case Sensitive Search.



SEARCH Function is similar to Find Function and is not a Case Specific.

Search Function is a In-Case Sensitive Function.



Syntax:

FIND(Find_Text,Within_Text,Start_Num) or

SEARCH(Find_Text,Within_Text,Start_Num)

Here :

Find_Text   is the text that you want to Find/Search.

Within_Text   is the text containing the text in which you want to Find/Search.

Start_Num   is the Search Position Number from which to Start the Search. The position of first character in Within_Text is 1.

If you omit Start_Num, it is assumed to be 1(i.e., Search starts from beginning.)



Note:

The Search Direction is Left-Right By Default.

Example:

Suppose if Cell A2 has the Main Text String , in which you want to find the position of the your desired string you can done in following way as explained in the following example.




Yüklə 137,16 Kb.

Dostları ilə paylaş:




Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©muhaz.org 2024
rəhbərliyinə müraciət

gir | qeydiyyatdan keç
    Ana səhifə


yükləyin