Excel Match Function

Excel Match function is categorized under excel lookup and reference functions. It is used to fetch the value in an array and returns the position of the value within the array. For example, we need to match the value of 5 in a range comprising(1, 5, 6, 8, 9). The function will return 2, as 5 is the 2^nd item in the given range.

It is a built-in excel function and can be used as a worksheet function.

Syntax

=match (lookup_value, lookup_array, [match_type])

Arguments

• Lookup_value. REQUIRED. Value that we want to look up. Numeric/ text/ logical value/ cell reference. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
• Lookup_array. REQUIRED. Data array to search in.
• Match_type. OPTIONAL. Can be set to 1, 0, and -1. By default, match function takes 1 as match type.

Match_type:

• 1 It returns the largest value that is less than or equals to the lookup value. It requires sorting of lookup array in ascending order, from smallest to largest; a to z.
• 0 Finds the first value in the array that exactly matches with the lookup value. No sorting is required.
• -1 returns the smallest value that is greater than or equals to the lookup value. Lookup array should be sorted in descending order; from largest to smallest; z to a.

Purpose

To find the relative position of lookup_value in a row, column, table or an array. For partial matches, wildcard characters are used.

Return Type

Integer

Return Value

Position of the lookup_value.

Remarks

• If match function does not find a match, it returns #N/A error.
• Match Function is not case-sensitive.
• If match_type = 0 and lookup_value is a text value, you can use wildcards in the lookup_value.

1. * is used to match any sequence of characters
2. ? is used to match any single character

• Use wildcards when match_type is set to 0.
• If lookup_value has several occurrences in the lookup_array, it returns the position of the first value.
• In financial analysis, match function is used in combination with other functions to lookup and return the values in the column. Commonly it is used with the INDEX function, generally called INDEX MATCH combination. Match returns a position, to fetch the value, learn using Index and Match together.

Sources:

• https://www.techonthenet.com/excel/formulas/match.php
• https://exceljet.net/excel-functions/excel-match-function
• https://support.office.com/en-us/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a

Excel Choose Function

It is a built-in lookup/ reference function that is used to get the value using a giving position or index. Also, it can be used as a worksheet function and a VBA function in excel.

Syntax

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

Arguments

• Index_num. REQUIRED. The position number in the list of values to return. A number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254. Limit of 29 in EXCEL 2003.
• Value, Value2… Value1 is required, subsequent values are optional. A value can be a number, a cell reference, a defined name, a formula/function, or a text value. 1 to 254 value arguments from which Choose selects the a value or an action to perform based on index_num.

Purpose

To get a value from a list based on position

Return Value

The value at the given position

Return Type

string, numeric, date, etc.

Remarks

• If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
• If index_num is a fraction, it is truncated to the lowest integer before being used.
• If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
• If index_num is an array, every value is evaluated when CHOOSE is evaluated.
• The value arguments to CHOOSE can be range references as well as single values.

So, to get the value from the list using position, we use CHOOSE function. However, to get the position of a value in a row/ column/ table, use MATCH Function. Choose can handle up to 254 values but INDEX, or INDEX with MATCH are better approaches for large value sets.

Sources

• https://support.office.com/en-us/article/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc
• https://exceljet.net/excel-functions/excel-choose-function

Excel EOMONTH Function

A built-in excel time/date function, EOMONTH is used to calculate the last date of the month, n months in future of past. The result is a serial date value. It is worksheet function and can be entered as a part of other excel formulas. The function is good to fetch the due dates, expiration dates and other dates that need to land on the last date of the month.

Purpose

To find the last date of the month. Last date of the same month, previous months or future months

Syntax

=EOMONTH(start_date, month)

Arguments

• Start_date.REQUIRED. Starting date for reference
• Month. REQURED.Number of months to be added in the start date. Value could be 0, +ve or –ve.

Return Type

Date 

Return Value

Serial date value

Remarks

• If a decimal value is provided for months, the EOMONTH function will only add the integer portion to start_date.
• The EOMONTH function returns a serial date value, the way excel stores dates internally
• #NUM! error occurs when

1. Start_date is not a valid excel date
2. The supplied start_date plus the value of “months” argument is not a valid excel date.

• #VALUE error occurs when any of the supplied arguments is non-numeric.

Excel WORKDAY.INTL Function

Excel WORKDAY.INTL Function

A built-in excel function that is categorized as date/ time function, WORKDAY.INTL can be used as a worksheet function. It is introduced in MS Excel 2010 version. The function takes a date as input and returns a workday in future or past depending on the offset value you provide. The only different between Workday and Workday.INTL is that the Workday.INTL allows us to customize the weekend days. It is used to calculate completion dates, ship dates, etc. that take into account non-working days.

Purpose

To get N workday in past or future.

Return Value

Next or previous working date based on inputs.

Return Type

The WORKDAY.INTL function returns a serial date value representing the number of days since January 1, 1900

Syntax

Workday.INTL(start_date, days, weekend, holidays)

Arguments

• Start_date. date from which working days needs to count
• Workday function excludes the start date while fetching the workday.
• Days. +ve for future working date and –ve for past working date, a zero value yields the start_date. If it is supplied as a decimal number, it is truncated to the integer.
• Weekend. Optional. It can be specified which day or days are weekend days. It can either be a number or a string as

Number	Weekend days
1 or omitted	Saturday, Sunday
2	Sunday, Monday
3	Monday, Tuesday
4	Tuesday, Wednesday
5	Wednesday, Thursday
6	Thursday, Friday
7	Friday, Saturday
11	Sunday only
12	Monday only
13	Tuesday only
14	Wednesday only
15	Thursday only
16	Friday only
17	Saturday only

What is Weekend String Here?

It is a series of seven “0s” or seven “1s” that represent the seven days of the week, beginning with Monday. 1 represents a non-working day and 0 is used to represent a workday.

Example:0000011 – Saturday & Sunday are weekend days

1000001- Monday and Sunday are weekend days

Holiday. It is optional. A list of date or an array constant of the serial numbers representing the dates that should be considered as non-working days. Excel only cares about holiday dates, not holiday names.

Points to Keep in Mind:

·         If start-Date /a date in holiday list is out of the current database value, WORKDAY.INTL returns #NUM! error.

·         If start_date plus day-offset yields an invalid date, WORKDAY.INTL returns #NUM! error.

·         If weekend string is of invalid length or contains invalid characters, , WORKDAY.INTL returns #VALUE! error. (string length is 7 containing 0 and 1)

·         Make the list of holiday absolute reference so that it won’t change when you paste the formula to other cells. Example. If your holidays are stored in the range G3:G6, it is best to enter the range as $G$3:$G$6.

Types of Errors That Workday and Workday.INTL Returns:

·         #NUM! error occurs if either:

A combination of the start_date and days arguments results in an invalid date, or

Weekend argument in the WORKDAY.INTL function is invalid.

·         #VALUE! error occurs if either:

start_date or any value in holidays is not a valid date, or

Days argument is non-numeric.

Excel Workday Function

Workday Function

Workday function returns a date, N working days in future or past and it can be used to add or subtract workdays to a given date. It calculates workday by excluding the start date. Workday function is used to calculate due dates, delivery dates, and other dates that should exclude non-working days.

Syntax

=workday(start date, days, holidays)

Arguments

• Start date- REQUIRED. Date from which working days needs to count
• Workday function excludes the start date while fetching the workday.
• Days- +ve for future working date and –ve for past working date.
• Holidays. Optional. A list of date or an array constant of the serial numbers representing the dates that should be considered as non-working days. Excel only cares about holiday dates, not holiday names.

*Date – be it a start date or a date in the holiday list- should be entered using Date Function or as result of other formula or functions. Problem can occur if date is entered as TEXT (#VALUE! Error occurs).

Points to Keep in Mind:

• If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.
• If days is not an integer, it is truncated.

By default, workday function excludes weekends as Saturday & Sunday for fetching the workday in future or past. To customize which day(s) of the week is/ are considered as weekend, use Excel WORKDAY.INTL Function. 

Excel Find Function

Find Function

Find function is used to fetch the position of a sub string in a string of characters. Sub string can be a single character or a group of characters existing in a string. It is a built-in, string/ text function in excel and can be used as a worksheet function.

Syntax:

=FIND(sub string, string, start_position) or =FIND(find_text, within_text, start_position)

Purpose:

To fetch the location of a text in a string

Return Value:

A number representing the location of sub string

Arguments:

• Sub string: The text or a cell containing text that you want to find
• String: The text string to search within. Usually it is a cell reference but string can also be typed in the formula
• Start_position: An argument that specifies from which character the search will begin. It is optional. If omitted, FIND will count from the first character of the string.

*Remember start_position only specifies the search start position. Result will be counted from the starting of the string.
For example: In a string of 9 characters, you need to fetch the position of a character at 7th place. And you put 5 as the start_position.
In this case, FIND will search from the 5th character and position will be counted from the first character of the string. So, answer will be 7*

Excel FIND function - things to remember!

To get the desired result using excel Find function; keep the following points in mind:

• FIND function is case-sensitive, use SEARCH function for case-insensitive match
• FIND function does not allow using wildcard characters
• If sub string contains several characters, FIND returns the position of the first character in the sub string
• If string contains several occurrences of sub string, FIND returns the position of the first occurrence
• If sub string is an empty string means “”, FIND returns the position of the first character in the search string
• FIND function returns #VALUE! Error, when:

1. Sub string does not exist in the string
2. Start_position is more than the number of characters of the string
3. Start_position is zero or a negative value

Reasons Why Your Business Needs A Digital Marketing Consultant

Do you own a business? Are you finding it difficult to drive rapid and sustainable business growth? Are you one of the companies that don’t have a digital marketing strategy yet? Do you have plenty of reasons to not get help for digital marketing strategy planning? Well, here are a few reasons that entail hiring a digital marketing consultant is crucial to succeed in today’s marketplace. Scroll down to read more!

#1. The Digital Revolution

With digital marketing, businesses are no longer limited by geography, time-zone or any of the constraints of traditional marketing methods. Digital technology allows businesses to communicate their marketing message with absolute laser-like precision by targeting, segmenting and positioning specific niche market segments. All that is required is a professional that can plan and implement different marketing techniques to get better outcomes.

#2. You Lack Strategic Direction

If a business wishes to lead the digital world, it is imperative to have clear aims & objectives and a well-developed brand vision. A consultant can be useful for planning clear action plans to make everything happen.

#3. You Want To Dominate The Digital Landscape

If a business wants to become a preeminent name in the industry and dominate the digital landscape, then there is a need of coherent digital marketing strategy. When it comes to digital marketing, the prime focus should be on building marketing gravity that amplifies the market reach with an aim of attracting people to the unique brand value. And, this all can be easily done with the help of a professional digital marketing consultant.

#4.You’re Behind Your Competitors

If your business hasn’t got a clear vision for the future and it’s using an ad-hoc approach with no coherent strategy, then there are chances to fall behind. To avoid such a chaotic situation, it is good to appoint a professional digital marketer.

#5. You Don’t Know Your Unique Brand Value

Understanding your unique brand value will facilitate you to differentiate your offerings and clearly communicate it to your prospective customers so that they do not miss out on the fantastic value that you have to offer them. So, it is advised to get associated with a digital marketing consultant before you start digital marketing.

#6. Your Marketing In Not Integrated

Do not consider digital marketing as a separate function from the rest of the business. It is an integrated approach that can help you achieve your organizational goals and improve ROI in both online and offline channels. All this can be achieved conveniently with the help of a digital marketer.

#7. You Are Not Organized

Taking an ad-hoc approach to digital marketing means you are probably not equipped to respond to the changes that are taking place in the industry right now. In order to take full advantage of the opportunities available in the digital world, investment in resource planning and implementation is vital and it can be made with the help of digital marketing consultant.

If there is no strategy, digital marketing will not benefit you no matter how much time, money or resources you invest. Before approaching a digital marketing consultant, it is advised to measure your digital marketing requirements. If you own a small business, you can go for freelance digital marketing professionals; for the middle-scale firm, you can outsource your digital marketing requirements and for a blue-chip company, you can have an in-house digital marketing team.