Trending February 2024 # Workday In Excel (Formula, Examples) # Suggested March 2024 # Top 9 Popular

You are reading the article Workday In Excel (Formula, Examples) updated in February 2024 on the website Minhminhbmm.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Workday In Excel (Formula, Examples)

WORKDAY Function in Excel (Table of Contents)

WORKDAY in Excel

Workday function in excel returns the Date, which is the official working day from the date which we feed into the syntax. This is quite useful for getting what would the working day date after selective day counts. As per syntax, we just need to select the date from which we need to count the number of the working day, then select how many days we need to count, and if there is any week off, we have optional. If we select today’s date with 5 days and 2 weeks off days, we will get the date of the same weekday.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

WORKDAY Formula in Excel:

Below is the WORKDAY Formula in Excel.

Explanation of WORKDAY Formula in Excel

A WORKDAY Function in Excel includes two mandatory parameters and one optional parameter.

Start_date: “Starting date of the project or any work”.

Days: The total number of days required to complete the work or project. This does not include weekends (Saturday and Sunday).

[Holidays]: This is an optional parameter. This section asks whether the days you have mentioned include any holidays. For this, you need to make a list of holidays separately.

WORKDAY Function in Excel by default excludes Saturday and Sunday as weekend days. If at all you need weekends for any other day, you can use chúng tôi function. For example: In the Middle East region, weekend days are Friday & Saturday. In these cases, we can use chúng tôi function instead of a normal WORKDAY Function in Excel.

How to Use WORKDAY Function in Excel?

You can download this WORKDAY Function Excel Template here – WORKDAY Function Excel Template

Example #1

Using the WORKDAY Function in excel, we can generate a series of dates even though we can generate by using the drag and drop option.

Step 1: Enter the one date on cell A2 as 12/Nov/2024.

Step 2: Now, in cell A3, apply the WORKDAY Function as shown in the below image.

=WORKDAY(A2,1)

The above formula takes the cell A2 as a reference and increases the date by 1.

Step 3: Drag the formula until cell A18.

Look at the formula here; 12/Nov/2024 is on Monday; we are increasing the day by 1. When we drag the formula, it will increment the date by 1 until 16/Nov/2024. If you drag one more time, it will jump to 19/Nov/2024 and excludes 17/Nov/2024 and 18/Nov/2024; those are weekends.

Similarly, in the next week, workdays are from 19/Nov/2024 to 23/Nov/2024, and weekends are 24/Nov/2024 and 25/Nov/2024.

Example #2

The project starting date and project duration date calculate the project ending date by using a WORKDAY Function in Excel.

Note: No holidays apply to these projects.

Step 1: Copy and paste the above data to an excel sheet.

Step 2: Apply the WORKDAY Function in column C starting from cell C2.

=WORKDAY(A2,B2)

Result is :

Example #3

Consider the above example data for this also. But here, the list of holidays is available to estimate the project ending date.

The list of holidays are:

Apply the same formula as shown in example 2, but here you need to add one more parameter, i.e. holidays.

=WORKDAY(A2,B2,$G$2:$G$21)

Result is :

Example 2 vs Example 3:

Now we will see the difference between the two examples.

In the second example for the first project-ending date is 8/8/2024, and there is one holiday, for example, 3, so the ending date increased by 1 day.

For the second project, the ending date is 30/01/2024, for example, 2 and example 3; there 5 holidays, so the ending date increased by 7 days because of the in-between weekend.

Example #4

Assume you are working in the Accounts Receivable team; you have a list of invoices and due dates against those invoices. You need to find the due days for those invoices.

=WORKDAY(A2,B2)

Result is :

Initially, the result looks like serial numbers. We need to change our formatting to make it correct.

Step 1: Select the entire range.

Step 2: Now press ctrl +1. It will open up a formatting dialogue box.

Step 4: Your result looks like the below one.

Things to Remember

If you want to use different weekends other than Saturday and Sunday, use chúng tôi function.

We can use only numeric values for the day’s argument.

The date and days should be accurate otherwise;, we will get the error as #VALUE!

If the date includes time, then the formula considers only the date portion and ignores the time portion.

If you supply decimal numbers, a formula will round down the value. For example: if you supply 10.6 days, then the formula treats this as 10 days only.

Recommended Articles

This has been a guide to WORKDAY ID in Excel. Here we discuss the WORKDAY Formula in Excel and how to use the WORKDAY Function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

You're reading Workday In Excel (Formula, Examples)

Energy Stored In A Capacitor � � Formula And Examples

A capacitor is an electronic circuit component that stores electrical energy in the form of electrostatic charge. Thus, a capacitor stores the potential energy in it. This stored electrical energy can be obtained when required. Ideally, a capacitor does not dissipate energy, but stores it.

A typical capacitor consists of two metallic plates separated by an insulating material, called dielectric. When these two metallic plates of the capacitor are connected to a source of electrical energy, the capacitor starts charging and stores electrical energy in its dielectric. Therefore, it is important to derive the expression of this stored energy in the capacitor so that we can select a suitable capacitor for our circuit designing.

Energy Stored in a Capacitor

As discussed above, a capacitor stores electrical energy in the form of electrostatic charge. Thus, a charged capacitor produces an electrostatic field. When the capacitor of capacitance C farad is connected across a battery of V volts as shown in Figure-1. In this situation, the entire battery voltage V is applied across the capacitor plates. As a result, plate A of the capacitor becomes positively charged while plate B becomes negatively charged. This potential difference between the two plates establishes an electric field directed from plate A to B through the dielectric material of the capacitor.

Due to this electric force, the end of the dielectric near the positive plate will become negatively polarized, while the end near the negative plate will become positively polarized. Consequently, there is an electrostatic charge (and electrostatic field) is created within the capacitor. In this condition, the capacitor is said to be charged and stores a finite amount of energy.

Now, let us derive the expression of energy stored in the capacitor. For that, let at any stage of charging, the electric charge stored in the capacitor is q coulombs and the voltage the plates of the capacitor is v volts. Then,

$$mathrm{qpropto v}$$

$$mathrm{Rightarrow q=C v}$$

By the definition of voltage, a work of v Joules is required to be done in storing a charge of 1 Coulomb in the capacitor. Hence, for storing a charge of dq Coulombs in the capacitor, the work done is,

$$mathrm{dW=v, dq}$$

$$mathrm{Rightarrow dW=v, dleft ( Cv right )}$$

$$mathrm{therefore dW=Cv, dv}$$

Integrating on both side to get the total work done in raising the voltage of the uncharged capacitor to V volts.

$$mathrm{W=Cint_{0}^{v}v, dv=Cleft [ frac{v^{2}}{2} right ]_{0}^{v}}$$

$$mathrm{therefore W=frac{1}{2}CV^{2}}$$

This work done will be stored in the capacitor in the form of potential energy (electrostatic field).

Also,

$$mathrm{C=frac{Q}{V}: and: V=frac{Q}{C}}$$

Thus, the energy stored in the capacitor can also be given by,

$$mathrm{W=frac{1}{2}QV=frac{1}{2}frac{Q^{2}}{C}}$$

The energy stored in the capacitor will be expressed in joules if the charge Q is given in coulombs, C in farad, and V in volts.

From equations of the energy stored in a capacitor, it is clear that the energy stored in a capacitor does not depend on the current through the capacitor.

Note − A pure or ideal capacitor does not dissipate energy, instead, it stores energy and returns the stored energy when delivering power to the circuit.

Numerical Example (1)

A capacitor has a capacitance of 0.5 μF is connected across a battery of 120 V. Determine the energy stored in the capacitor.

Solution

Given data,

𝐶 = 0.5 μF = 0.5 × 10−6F

𝑉 = 120 V

The energy stored in the capacitor will be,

$$mathrm{W=frac{1}{2}CV^{2}=frac{1}{2}times left ( 0.5times 10^{-6} right )times 120}$$

$$mathrm{therefore W=3times 10^{-5}J=30, mu J}$$

Numerical Example (2)

When a capacitor is connected to a source of 240 V, it stores a charge of 50 mC. Calculate the energy stored in the capacitor.

Solution

Given data,

Voltage, 𝑉 = 240 V

Charge, 𝑄 = 50 mC = 50 × 10−3 C

The energy stored in the capacitor is given by,

$$mathrm{W=frac{1}{2}QV=frac{1}{2}times left ( 50times 10^{-3} right )times 240}$$

$$mathrm{therefore W=6, Joules}$$

Conclusion

From the above discussion, it is clear that a capacitor stores electrical energy in the form of electrostatic field, and this stored energy is referred to as potential energy because it is due to the difference of potential.

From the expression of stored energy in a capacitor, it is clear that the energy stored is directly proportional to capacitance of the capacitor, which means a capacitor of higher capacitance can store more amount of energy for the same voltage and vice-versa.

Due to their energy-storing property, capacitors are used in several electrical and electronic circuits such as chargers, capacitor banks, computer circuits, etc.

How To Calculate? (Mathematical & Excel Formula)

What is the Median Formula?

The median formula finds the middle value in a set of data, i.e., the middle number in a set of numbers. It tells us where the center of the data is, which can be helpful in different scenarios like studying temperatures, analyzing student scores, or finding the central age in a population.

The median formula is useful when a dataset is uneven or has extreme numbers (values that are much higher or lower than the others). In such cases, other methods, like the mean, can give a less accurate result as extreme values can influence the center value. However, the median formula is less affected by these outliers and provides a more precise estimate of the middle value.

Start Your Free Investment Banking Course

Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others

We can calculate the median using a simple mathematical formula:

Median = (n + 1) / 2

Where “n” represents the total number of items, i.e., the total number of values present in the dataset.

Note: This formula helps us find the position of the median in the dataset rather than the actual value of the median.

How to Calculate Median in Excel?

We can use the built-in function called “MEDIAN” to calculate the median in Excel.

The syntax for the MEDIAN function is as follows:

1. Range of Dataset

Here, we must add the cell range for the data in the function as the first argument.

For example, if our data is present in the cells A2 to A 10, we will write the MEDIAN function as:

=MEDIAN(A2:A10)

2. Array of Data

In this approach, we directly provide all the dataset values to the MEDIAN function in the form of an array. You can use this method when the dataset is small, or else directly selecting the range is an easy option.

For example, if our dataset has the following values: 2,4,6,8,10, our MEDIAN function will be:

=MEDIAN(2,4,6,8,10)

Note: Unlike the mathematical formula for calculating the median, the MEDIAN Excel function gives the actual value of the median rather than the position.

Examples of Median Formula

Let’s take a look at some examples of median formulas. We have provided examples of both the Simple median formula as well as MEDIAN function in Excel.

1. Simple Median Formula 

2. MEDIAN Function in Excel

We have included an Excel template with solved examples to help you better understand how to use the median function and formula in Excel.

You can download this Median Excel Template here – Median Excel Template

1. Simple Median Formula Examples Example #1: Odd Number of Observations

Calculate the Median for the dataset: A= 42, 21, 34, 65, 90, 45, 109. As the number of values in the dataset is 7, n will be 7.

To sort the data from the smallest to the highest number, you have 2 options:

Therefore, the median value for the dataset sorted in ascending order (21, 34, 42, 45, 65, 90, 109) is 45.

Example #2: Even Number of Observations

Step 3: Find the Median

Thus, the median value for the dataset is 36.5.

2. MEDIAN Function in Excel Examples Example #1: Dataset with Odd Number of Values

A company has the following sales data for the years 2014 to 2023.

Year

2014 2024 2024 2024 2024 2024 2024 2024 2024

Sales

141 177 157 191 211 206 214 202 212

Let us find the median for the above data using the MEDIAN function.

While using the MEDIAN function, there is no need to sort the data in ascending order. The function does this automatically before finding the median.

Step 1: To find the median, add the following formula in the cell B12:

=MEDIAN(B2:B10)

Step 2: Press “Enter”. The MEDIAN function finds and returns the median value.

So, the median for the given sales data is 202.

Example #2: Dataset with an Even Number of Values

C = 13, 25, 12, 40, 27, 80, 55, 62, 99,73

Step 1: Enter the folding formula in the cell B13:

=MEDIAN(13, 25, 12, 40, 27, 80, 55, 62, 99,73)

Here, the function returns the value of 47.50. It is the average between the two middle values of the dataset (40 and 55).

Therefore, the median for the dataset (13, 25, 12, 40, 27, 80, 55, 62, 99,73) is 47.5.

Things to Keep in Mind

If you want to find the number of values in a large dataset containing hundreds or thousands of values, you can use the COUNTA function to find the ‘n’ value.

Example: Let’s say you want to find the median for a dataset present in cells A1 to A1000. To quickly and easily find the value for ‘n’, you can use the formula: =COUNTA(A1:A1000)

While calculating the median, the MEDIAN function will automatically ignore any cell with a non-numeric value, i.e., if the cell has text or is empty.

Example: Suppose we have a dataset in cells A1 to A7, where A3 is empty, and A6 contains the text “eduCBA”, the formula “=MEDIAN(A1:A7)” will only consider the numeric values (1, 2, 4, 5, 7) and ignore the empty cell and text.

The MEDIAN function takes into account logical values (TRUE and FALSE) during the calculation. TRUE is treated as 1, while FALSE is treated as 0.

Example: If you use the MEDIAN function for the dataset: FALSE, 5, TRUE, 2, the median will be 3. It is the average of the two middle values, 5 and TRUE (equivalent to 1). However, if the dataset was FALSE, 5, 2, the median would be 5 (not 3).

Importance and Uses of Median Formula

The Median is the point where 50% of the numbers are above & 50% of the numbers are below. It is a centrality denoting the middle value, which is helpful for a historical dataset or data set over time.

Economists use median values to draw conclusions and frame economic policies. For instance, they can determine the median price of houses in a specific area to see if houses in that area are affordable or expensive.

Median Formula Calculator

Use the following medium calculator for calculating the median.

n Median Formula   Median Formula = (n + 1) = 2 (

0

+ 1) = 0 2

Frequently Asked Questions(FAQs)

The value of “n” would be 7 because there are 7 values in the dataset.

Answer: In statistics, the mean, median, and mode have a connection known as the empirical relationship. It means that if we know the value of any of the two metrics, we can find the value of the remaining one. We can use the following equation to find this:

Mode = 3 x Median – 2 x Mean

Mode = 6

Answer: We have two formulas for the median, i.e., the odd number of observations and the even number of observations.

If the number of terms(n) is even, then the formula is ½ [(n/2)+((n/2)+1)]

If the number of terms(n) is odd, then the formula is (n+1)/2

Answer: If you want to find the median for a group of data based on a condition, you can use the conditional median formula. For instance, if you want to find the median for all values that are below 100, we can use the conditional median formula. Here we use the IF function combined with the MEDIAN function. The IF function checks if a given condition is true for each value in the dataset. If the condition is true, the MEDIAN function includes that value when determining the median of the dataset.

The syntax is as follows:

MEDIAN (IF(criteria_range = criteria, median_range))

Recommended Articles

This article shows how to calculate the median in Excel using the median formula and the median Excel function. We have explained each formula and how to use them and given solved examples with a downloadable Excel template. You can also use the calculator to directly find the median value for a dataset. You may also look at the following articles to learn more,

Sum By Color In Excel (Examples)

Sum by Color in Excel

In this article, we will learn about Sum By Color in Excel. In Excel, we have a function for adding numbers. But there is no direct way to add the number by their background color. By this, we don’t need to sum the numbers separating the colored cells. We can directly consider all the cells in the formula and sum them as per their background color.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

This we can do when we have many cell numbers colored, and filtering the data is not suggested there.

How to Sum by Color in Excel?

Excel Sum by Color is very simple and easy. Let’s understand how to sum by color in Excel with some examples.

You can download this Sum by Color Excel Template here – Sum by Color Excel Template

Sum by Color in Excel – Example #1

Here we have data on some product and their sale. As shown below, column C has numbers with some background color.

Now, as we need to sum the numbers, so from the drop-down of SUBTOTAL Function, select 9, which is for sum.

And for reference1, select the complete range of column C, which we need to total as shown below.

The Output will be as given below.

Now apply the filter in the top row by pressing Ctrl + Shift +L.

Go to Filter by Color from the drop-down menu of it. Select any color; we have selected YELLOW, as shown below.

Once we do that, we will get the Output cell filtered sum as 190, as shown below.

We can also check the correctness of the applied SUBTOTAL formula by filtering the different colors.

Sum by Color in Excel – Example #2

There is another way to sum the numbers by their colors. For this, we will consider the same data as shown in example-1. Now copy the column’s cells with numbers and paste them into a separate sheet or in the same sheet in a different location.

Once we do that, selected cells will convert into the table form. And another menu will add with the name Design in the menu bar. Now Check and tick the Total Row option from the Table Style Options.

Once we do that, we will get the sum of cells at the bottom end of the column with a drop-down menu. Here we are getting a sum of 786.

Now from the drop menu of the total sum, select the Sum option as shown below.

By this, we enable the table to sum the filtered data as per colored cells. Now go to the top filter drop-down of the same column and select any color to get summed up from the Filter by Color option. Select any color; we have selected YELLOW, as shown below.

Once we do that, we will get the YELLOW colored filtered and the sum of the YELLOW colored cells in the below cell.

Sum by Color in Excel – Example #3

There is another method of summing the numbers by their color. VBA Marcos will do this. For this, we will consider the same data we saw in example-1. And we will add separate cells for each product name to get the sum of their quantity sold.

Now press Alt + F11 to enter Visual Basic for the Application screen.

Now go to the Insert menu and select Module.

This will open a new Module to write code. Now in the blank Module, write the code for enabling the sum by color function in Excel, as shown below. You can also use the same code to make some changes in that.

Close the complete window of VBA. Now go to the cell reference of Mobile, where we need to see the result and type the “=” sign. Now search and select the Sum Color function we created in VBA.

And select the reference colored cell and then select the range to get summed, as shown below.

The Result will be as shown below.

Once done, drag the formula to complete respective cells to see the result as shown below.

As we can see in the above screenshot, the sum of yellow-color cells is coming at 190, which the summed value is obtained in example-1 and example-2. This means that all the formulas and functions used in all examples are correct.

Pros

Sum by color from the SUBTOTAL function is the easiest way to get the sum result by color in Excel.

The process steps shown in example-2 take a little longer than in example-1, but it is still easy to apply.

We don’t need to filter the colored cells separately to get the sum.

Cons

Sum by color shown in example-3 by VBA coding takes time, and it doesn’t show the result if we paste the data in another file because it does carry the code with it.

Things to Remember About Sum by Color in Excel

If you are summing colored cells by VBA Coding, it is always recommended to save in the Macro enabled Excel; this will save the coding for future use.

These methods can use anywhere, irrespective of the data size. It is always recommended to use this method when we have a huge set of data, where if we filter the data to get the summed value may crash the file.

Recommended Articles

This has been a guide to Sum by Color in Excel. Here we discuss how to sum by color in Excel, practical examples, and a downloadable Excel template. You may also look at the following articles to learn more –

Accounting Number Format In Excel

Excel Accounting Number Format (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Accounting Number Format in Excel Difference between Currency and Accounting Number Format in Excel

Accounting Format:

The difference between Currency and Accounting format is shown in the below screenshot.

How to Use Accounting Number Format in Excel?

In Microsoft Excel, we can find the accounting format under the number formatting group shown in the below screenshot.

Also, we can format the number in accounting format by choosing the dollar sign $ in the number group, which is also one of the shortcuts for the accounting number format shown in the below screenshot.

Example #1

Converting Number to Excel Accounting Format

You can download this Accounting Number Format Excel Template here – Accounting Number Format Excel Template

In this example, we will learn how to convert the normal number to accounting format. Consider the below example, which shows MRP, Selling Price of the individual product with local, national and zonal prices.

As we can notice that all the numbers are in general format by default, Assume that we need to convert the “Selling Price” to Accounting Number format along with Local, Zonal, and National selling prices.

In order to convert the number to Accounting format, follow the below procedure step by step.

First, select the column from E to H, where it contains the product’s selling price, which is shown in the below screenshot.

Once we choose the Accounting number format, we will get the output as ###, which is shown below.

We can notice that once we convert the number to accounting number format, excel will align the dollar sign at the left edge of the cell and display with two decimal points that we are getting the ### hash symbols.

Enlarge all the columns so that we can see the exact accounting format output, which is shown below.

In the below result, we can see that all the numbers are converted where we can see the Dollar sign$ in each left edge of the cell separated by commas and with two decimal numbers. 

Example #2

To apply accounting number formatting, follow the below step by step procedure as follows.

First, select the column from E to H, where it contains the product’s selling price, which is shown in the below screenshot.

In the above screenshot, we can see the list of number formatting options.

Select the Accounting option so that it will display the accounting format, which is shown below.

As we can see, on the right-hand side, we can see decimal places where we can increase and decrease the decimal points, and next to that, we can see the symbol drop-down box, which allows us to select which symbol needs to be displayed. (By default, accounting format will select the Dollar Sign $)

Once we increase the decimal places, the sample column will display the number with selected decimal numbers which are shown below.

Example #3

This example shows how to sum the accounting number format by following the below steps.

Consider the example which shows sales data for the month of OCT-18.

As we can see that there are normal sales figures in the General number format. Now we will convert the above sales figure to accounting format for accounting purposes.

First, copy the same B column sales figure next to the C column, which is shown below.

Now select the C column and go to the number formatting group and choose Accounting, shown below.

As we can see, the difference that C column has been converted to accounting format with a Dollar sign with two decimal places and at the last column for negative numbers accounting format has shown the number inside the parenthesis.

Put the SUM formula in the C13 column, which will show the SUM in accounting format.

In the below result, we can see that the accounting format which automatically uses the dollar sign, decimal places, and comma to separate a thousand figures where we cannot see those in General number format.

Things to Remember 

The accounting number format is normally used for financial and accounting purposes.

The accounting number format is the best way to configure the values.

For negative values accounting format will automatically insert the parenthesis.

Recommended Articles

This has been a guide to Accounting Number Format in Excel. Here we discussed how to use Accounting Number Format along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

Index Match Functions In Excel

In this tutorial, we’ll dive into the powerful Excel INDEX and MATCH functions, which are essential for manipulating and analyzing large sets of data.

We’ll start by exploring what these functions do and how they retrieve specific information from a table, and then we’ll write INDEX and MATCH formulas together as an alternative to the VLOOKUP formula.

We’ll also cover some practical use cases for INDEX and MATCH formulas.

Note: if you have Excel 2023 or later, or Microsoft 365 you should use the XLOOKUP function as this is easier and potentially more efficient.

Watch the INDEX and MATCH Video

Download the Excel File

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the workbook. Note: This is a .xlsx file. Please ensure your browser doesn’t change the file extension on download.

How the INDEX function works:

The INDEX function returns the value at the intersection of a column and a row.

The syntax for the INDEX function is:

=INDEX(

reference

,

row_num

,

[column_num]

, [area_num])

In English:

=INDEX( the range of your table, the row number of the table that your data is in, the column number of the table that your data is in, and if your reference specifies two or more ranges (areas) then specify which area*)

*Typically only one area is specified so the area_num argument can be omitted. The examples below don’t require area_num.

INDEX will return the value that is in the cell at the intersection of the row and column you specify.

For example, looking at the table below in the range B17:F24 we can use INDEX to return the number of program views for Bat Man in the North region with a formula as follows:

=INDEX(

B17:F24

,

2

,

3

)

The result returned is 91.

On its own the INDEX function is pretty inflexible because you have to hard key the row and column number, and that’s why it works better with the MATCH function.

Note: You may have noticed that the INDEX function works in a similar way to the OFFSET function, in fact you can often interchange them and achieve the same results.

How the MATCH function works:

The MATCH function finds the position of a value in a list.  The list can either be in a row or a column.

The syntax for the MATCH function is:

=MATCH(

lookup_value

,

lookup_array

,

[match_type]

)

Now I don’t want to go all syntaxy (real word 🙂 ) on you, but I’d like to point out some important features of the [match_type] argument:

The match_type argument specifies how Excel matches the lookup_value with values in lookup_array. You can choose from -1, 0 or 1 (1 is the default)

[match_type] is an optional argument, hence the square brackets. If you leave it out Excel will use the default of 1, which means it will find the largest value that is

0 will find the first value that is exactly equal to the lookup_value. The values in the lookup_array can be in any order.

Ok, that’s enough of the syntax.

In English and using the previous example:

=MATCH(

find what row Bat Man is on

,

in the column range B17:B24

,

match it exactly (for this we'll use 0 as our argument)

)

The result is row 2.

We can also use MATCH to find the column number like this:

=MATCH(

find what column North is in

,

in the row range B17:F17

,

match it exactly (again we'll use 0 as our argument)

)

The result is column 3.

So in summary, the INDEX function returns the value in the cell you specify, and the MATCH function tells you the column or row number for the value you are looking up.

INDEX MATCH Together:

The INDEX and MATCH functions are a popular alternative to the VLOOKUP. Even though I still prefer VLOOKUP as it’s more straight forward to use, there are certain things the INDEX + MATCH functions can do that VLOOKUP can’t.  More on that later.

Using the above example data we’ll use the INDEX and MATCH functions to find the program views for Bat Man in the East region.

=INDEX(

the range of your table

,

replace this with a MATCH function to find the row number for Bat Man

,

replace this with a MATCH function to find the column number for East

)

The formula will read like this:

=INDEX(

return the value in the table range B17:F24 in the cell that is at the intersection of

, MATCH(

the row Bat Man is on

) and, MATCH(

the column East is in

)

The formula looks like this:

=INDEX(

$B$18:$F$24

,MATCH(

"Bat Man",$B$18:$B$24,0

), MATCH(

“East”,$B$17:$F$17,0

))

So why would you put yourself through all that rigmarole when VLOOKUP can do the same job.

Reasons to use INDEX and MATCH rather than VLOOKUP

1) VLOOKUP can’t go left

Taking the table below, let’s say you wanted to find out what program was on the Krafty Kids channel.

VLOOKUP can’t do this because you’d be asking it to find Krafty Kids and then return the value in column B to the left, and VLOOKUP can only look to the right.

In comes INDEX and MATCH with a formula like this:

=INDEX($B$33:$B$40,MATCH("Krafty Kids",$C$33:$C$40,0))

And you get the answer; ‘Mr Maker’.

Notice only the Programs column (B) was referenced in INDEX’s array argument? This means we can omit INDEX’s column number argument as there’s only one column in the INDEX array.

2) Two way lookup

The table below has a drop down list in B1 that enables me to choose the Sales Person from the table, and a drop down list in A2 for the region.  In B2 I’ve got an INDEX + MATCH formula that returns the sales that match my two criteria.

=INDEX(A4:J10,MATCH(A2,A4:A10,0),MATCH(B1,A4:J4,0))

Note: An alternative is to use a VLOOKUP and replace the hard keyed column number with a MATCH formula like this:

Ways to improve these formulas:

1) Use named ranges instead of $C$33:$C$40 etc. to make formulas more intuitive and quicker to create.

2) An alternative to using a named range is to convert the data to an Excel Table whereby Excel automatically gives the table a named range.

3) If there is nothing else in the columns other than your table you could use column references like this C:C which will search the whole column.

Update the detailed information about Workday In Excel (Formula, Examples) on the Minhminhbmm.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!