*In this tutorial, you will find a number of formula examples that demonstrate the most efficient uses of INDEX in Excel.*

Of all Excel functions whose power is often underestimated and underutilized, INDEX would definitely rank somewhere in the top 10. In the meantime, this function is smart, supple and versatile.

So, what is the INDEX function in Excel? Essentially, an INDEX formula returns a cell reference from within a given array or range. In other words, you use INDEX when you know (or can calculate) the position of an element in a range and you want to get the actual value of that element.

This may sound a bit trivial, but once you realize the real potential of the INDEX function, it could make crucial changes to the way you calculate, analyze and present data in your worksheets.

There are two versions of the INDEX function in Excel - array form and reference form. Both forms can be used in all versions of Microsoft Excel 2019, 2016, 2013, 2010, 2007 and 2003.

The INDEX array form returns the value of an element in a table or an array based on the row and column numbers you specify.

INDEX(array, row_num, [column_num])

**array**- is a range of cells, named range, or table.**row_num**- is the row number in the array from which to return a value. If row_num is omitted, column_num is required.**column_num**- is the column number from which to return a value. If column_num is omitted, row_num is required.

For example, the formula `=INDEX(A1:D6, 4, 3)`

returns the value at the intersection of the 4^{th} row and 3^{rd} column in range A1:D6, which is the value in cell C4.

To get an idea of how the INDEX formula works on real data, please have a look at the following example:

Instead of entering the row and column numbers in the formula, you can supply the cell references to get a more universal formula: `=INDEX($B$2:$D$6, G2, G1)`

So, this INDEX formula returns the number of items exactly at the intersection of the product number specified in G2 (row_num) and week number entered in cell G1 (column_num).

- If the array argument consists of only one row or column, you may or may not specify the corresponding row_num or column_num argument.
- If the array argument includes more than one row and row_num is omitted or set to 0, the INDEX function returns an array of the entire column. Similarly, if array includes more than one column and the column_num argument is omitted or set to 0, the INDEX formula returns the entire row. Here's a formula example that demonstrates this behavior.
- The row_num and column_num arguments must refer to a cell within array; otherwise, the INDEX formula will return the #REF! error.

The reference form of the Excel INDEX function returns the cell reference at the intersection of the specified row and column.

INDEX(reference, row_num, [column_num], [area_num] )

**reference**- is one or several ranges.If you are entering more than one range, separate the ranges by commas and enclose the reference argument in parentheses, for example (A1:B5, D1:F5).

If each range in reference contains only one row or column, the corresponding row_num or column_num argument is optional.

**row_num**- the row number in the range from which to return a cell reference, it's similar to the array form.**column_num**- the column number from which to return a cell reference, also works similarly to the array form.**area_num**- an optional parameter that specifies which range from the reference argument to use. If omitted, the INDEX formula will return the result for the first range listed in reference.

For example, the formula `=INDEX((A2:D3, A5:D7), 3, 4, 2)`

returns the value of cell D7, which is at the intersection of the 3^{rd} row and 4^{th} column in the second area (A5:D7).

- If the row_num or column_num argument is set to zero (0), an INDEX formula returns the reference for the entire column or row, respectively.
- If both row_num and column_num are omitted, the INDEX function returns the area specified in the area_num argument.
- All of the _num arguments (row_num, column_num and area_num) must refer to a cell within reference; otherwise, the INDEX formula will return the #REF! error.

Both of the INDEX formulas we've discussed so far are very simple and only illustrate the concept. Your real formulas are likely to be far more complex than that, so let's explore a few most efficient uses of INDEX in Excel.

Perhaps there aren't many practical uses of Excel INDEX by itself, but in combination with other functions such as MATCH or COUNTA, it can make very powerful formulas.

All of our INDEX formulas (except for the last one), we will use the below data. For convenience purposes, it is organized in a table named ** SourceData**.

The use of tables or named ranges can make formulas a bit longer, but it also makes them significantly more flexible and better readable. To adjust any INDEX formula for your worksheets, you need only to modify a single name, and this fully makes up for a longer formula length.

Of course, nothing prevents you from using usual ranges if you want to. In this case, you simply replace the table name* SourceData* with the appropriate range reference.

This is the basic use of the INDEX function and a simplest formula to make. To fetch a certain item from the list, you just write `=INDEX(range, n)`

where *range* is a range of cells or a named range, and *n* is the position of the item you want to get.

When working with Excel tables, you can select the column using the mouse and Excel will pull the column's name along with the table's name in the formula:

To get a value of the cell at the intersection of a given row and column, you use the same approach with the only difference that you specify both - the row number and the column number. In fact, you already saw such a formula in action when we discussed INDEX array form.

And here's one more example. In our sample table, to find the 2^{nd} biggest planet in the Solar system, you sort the table by the *Diameter* column, and use the following INDEX formula:

`=INDEX(SourceData, 2, 3)`

`Array`

is the table name, or a range reference,*SourceData*in this example.`Row_num`

is 2 because you are looking for the second item in the list, which is in the 2^{nd}`Column_num`

is 3 because*Diameter*is the 3^{rd}column in the table.

If you want to return the planet's name rather than diameter, change column_num to 1. And naturally, you can use a cell reference in the row_num and/or column_num arguments to make your formula more versatile, as demonstrated in the screenshot below:

Apart from retrieving a single cell, the INDEX function is able to return an array of values from the **entire row** or **column**. To get all values from a certain column, you have to omit the row_num argument or set it to 0. Likewise, to get the entire row, you pass empty value or 0 in column_num.

Such INDEX formulas can hardly be used on their own, because Excel is unable to fit the array of values returned by the formula in a single cell, and you would get the #VALUE! error instead. However, if you use INDEX in conjunction with other functions, such as SUM or AVERAGE, you will get awesome results.

For example, you could use the following formula to calculate the average planet temperature in the Solar system:

`=AVERAGE(INDEX(SourceData, , 4))`

In the above formula, the column_num argument is 4 because *Temperature* in the 4^{th} column in our table. The row_num parameter is omitted.

In a similar manner, you can find the minimum and maximum temperatures:

`=MAX(INDEX(SourceData, , 4))`

`=MIN(INDEX(SourceData, , 4))`

And calculate the total planet mass (Mass is the 2^{nd} column in the table):

`=SUM(INDEX(SourceData, , 2))`

From practical viewpoint, the INDEX function in the above formula is superfluous. You can simply write `=AVERAGE(range)`

or `=SUM(range)`

and get the same results.

When working with real data, this feature may prove helpful as part of more complex formulas you use for data analysis.

From the previous examples, you might be under an impression that an INDEX formula returns values, but the reality is that it returns a **reference** to the cell containing the value. And this example demonstrates the true nature of the Excel INDEX function.

Since the result of an INDEX formula is a reference, we can use it within other functions to make a **dynamic range**. Sounds confusing? The following formula will make everything clear.

Suppose you have a formula `=AVERAGE(A1:A10)`

that returns an average of the values in cells A1:A10. Instead of writing the range directly in the formula, you can replace either A1 or A10, or both, with INDEX functions, like this:

`=AVERAGE(A1 : INDEX(A1:A20,10))`

Both of the above formulas will deliver the same result because the INDEX function also returns a reference to cell A10 (row_num is set to 10, col_num omitted). The difference is that the range is the AVERAGE / INDEX formula is dynamic, and once you change the row_num argument in INDEX, the range processed by the AVERAGE function will change and the formula will return a different result.

Apparently, the INDEX formula's route appears overly complicated, but it does have practical applications, as demonstrated in the following examples.

Let's say you want to know the average diameter of the N biggest planets in our system. So, you sort the table by *Diameter* column from largest to smallest, and use the following Average / Index formula:

`=AVERAGE(C5 : INDEX(SourceData[Diameter], B1))`

In case you want to define the upper-bound and lower-bound items in your formula, you just need to employ two INDEX functions to return the first and the last item you want.

For example, the following formula returns the sum of values in the *Diameter* column between the two items specified in cells B1 and B2:

`=SUM(INDEX(SourceData[Diameter],B1) : INDEX(SourceData[Diameter], B2))`

As it often happens, when you start organizing data in a worksheet, you may not know how many entries you will eventually have. It's not the case with our planets table, which seems to be complete, but who knows...

Anyway, if you have a changing number of items in a given column, say from A1 to A*n*, you may want to create a dynamic named range that includes all cells with data. At that, you want the range to adjust automatically as you add new items or delete some of the existing ones. For example, if you currently have 10 items, your named range is A1:A10. If you add a new entry, the named range automatically expands to A1:A11, and if you change your mind and delete that newly added data, the range automatically reverts to A1:A10.

The main advantage of this approach is that you do not have to constantly update all formulas in your workbook to ensure they refer to correct ranges.

One way to define a dynamic range is using Excel OFFSET function:

`=OFFSET(Sheet_Name!$A$1, 0, 0, COUNTA(Sheet_Name!$A:$A), 1)`

Another possible solution is to use Excel INDEX together with COUNTA:

`=Sheet_Name!$A$1:INDEX(Sheet_Name!$A:$A, COUNTA(Sheet_Name!$A:$A))`

In both formulas, A1 is the cell containing the first item of the list and the dynamic range produced by both formulas will be identical.

The difference is in the approaches. While the OFFSET function moves from the starting point by a certain number of rows and/or columns, INDEX finds a cell at the intersection of a particular row and column. The COUNTA function, used in both formulas, gets the number of non-empty cells in the column of interest.

In this example, there are 9 non-blank cells in column A, so COUNTA returns 9. Consequently, INDEX returns $A$9, which is the last used cell in column A (usually INDEX returns a value, but in this formula, the reference operator (:) forces it to return a reference). And because $A$1 is our starting point, the final result of the formula is the range $A$1:$A$9.

The following screenshot demonstrates how you can use such Index formula to create a dynamic drop-down list.

You can also use the INDEX function to create dependent drop-down lists and the following tutorial explains the steps: Making a cascading drop-down list in Excel.

Performing vertical lookups - this is where the INDEX function truly shines. If you have ever tried using Excel VLOOKUP function, you are well aware of its numerous limitations, such as inability to pull values from columns to the left of the lookup column or 255 chars limit for a lookup value.

The INDEX / MATCH liaison is superior to VLOOKUP in many respects:

- No problems with left vlookups.
- No limit to the lookup value size.
- No sorting is required (VLOOKUP with approximate match does require sorting the lookup column in ascending order).
- You are free to insert and remove columns in a table without updating every associated formula.
- And the last but not the least, INDEX / MATCH does not slow down your Excel like multiple Vlookups do.

You use INDEX / MATCH in the following way:

=INDEX (*column to return a value from*, (MATCH (*lookup value*, *column to lookup against*, 0))

For example, if we flip our source table so that *Planet Name* becomes the right-most column, the INDEX / MATCH formula still fetches a matching value from the left-hand column without a hitch.

For more tips and formula example, please see the Excel INDEX / MATCH tutorial.

Another smart and powerful use of the INDEX function in Excel is the ability to get one range from a list of ranges.

Suppose, you have several lists with a different number of items in each. Believe me or not, you can calculate the average or sum the values in any selected range with a single formula.

First off, you create a named range for each list; let it be *PlanetsD* and *MoonsD* in this example:

I hope the above image explains the reasoning behind the ranges' names : ) BTW, the *Moons* table is far from complete, there are 176 known natural moons in our Solar System, Jupiter alone has 63 currently, and counting. For this example, I picked random 11, well... maybe not quite random - moons with the most beautiful names : )

Please excuse the digression, back to our INDEX formula. Assuming that *PlanetsD* is your range 1 and *MoonsD* is range 2, and cell B1 is where you put the range number, you can use the following Index formula to calculate the average of values in the selected named range:

`=AVERAGE(INDEX((PlanetsD, MoonsD), , , B1))`

Please pay attention that now we are using the Reference form of the INDEX function, and the number in the last argument (area_num) tells the formula which range to pick.

In the screenshot below, area_num (cell B1) is set to 2, so the formula calculates the average diameter of *Moons* because the range *MoonsD* comes 2^{nd} in the reference argument.

If you work with multiple lists and don't want to bother remembering the associated numbers, you can employ a nested IF function to do this for you:

`=AVERAGE(INDEX((PlanetsD, MoonsD), , , IF(B1="planets", 1, IF(B1="moons", 2))))`

In the IF function, you use some simple and easy-to-remember list names that you want your users to type in cell B1 instead of numbers. Please keep this in mind, for the formula to work correctly, the text in B1 should be exactly the same (case-insensitive) as in the IF's parameters, otherwise your Index formula will throw the #VALUE error.

To make the formula even more user-friendly, you can use Data Validation to create a drop-down list with predefined names to prevent spelling errors and misprints:

Finally, to make your INDEX formula absolutely perfect, you can enclose it in the IFERROR function that will prompt the user to choose an item from the drop-down list if no selection has been made yet:

`=IFERROR(AVERAGE(INDEX((PlanetsD, MoonsD), , , IF(B1="planet", 1, IF(B1="moon", 2)))), "Please select the list!")`

This is how you use INDEX formulas in Excel. I am hopeful these examples showed you a way to harness the potential of the INDEX function in your worksheets. Thank you for reading!

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 76 responses to "INDEX function in Excel - 6 most efficient uses"

trying to use an existing spreadsheet's details to populate a different workbook dynamically, so that as the source workbook is updated (new lines inserted between existing data lines) these details transfer to the second workbook. The formula suggested is =INDEX('[MASTER Cluster Data Set.xlsx]Portfolio Clusters'!C4,1). The second workbook recognises that there is a new line to deal with but does not automatically display the row or details. Any suggestions what I am doing wrong? e-mail response would be appreciated.

Hello Sid,

Try use a named range with a few rows.

As sample: =INDEX(FirstBook.xlsx!ANYRANGE,6)

Hi there, I would like to find out how can i alternate functions such as Sum, Average etc when i have 10 rows with prices or values

Hello NKOSI,

You can use the IF function to switch between functions:

=IF(A1="SUM",SUM(B1:B10),AVERAGE(B1:B10))

https://support.ablebits.com/blog_samples/excel-index-function_2_switch_between_functions.xlsx

Hi there, I would like to find out how can i add a drop down list which will enable me to alternate functions such as Sum, Average etc when i have 10 values to add together or average?

NKOSI,

Use the IF function to switch between functions:

=IF(A1="SUM",SUM(B1:B10),AVERAGE(B1:B10))

https://support.ablebits.com/blog_samples/excel-index-function_2_switch_between_functions.xlsx

I am working with a similar formula with CTRL+SHIFT+ENTER and it works on the first cell, but when I copy down I get #NUM! errors?

{=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), ROW(1:1)))}

Any ideas? Would be greatly appreciated!

Hello, Erik,

For us to be able to assist you better, please send us a small sample table with your data in Excel and the result you expect to support@ablebits.com.

Hi, whatever row your formula is in, use this at the end of your formula ROWS($C$1:C1)... If your formula is being built inside of c1

Check your cell address.Change the range.Your format is ok but change the range brother.Thanks in advance.

Hi,

I failed to calculate via Index function the "Min" of dates array;

=INDEX(MIN('Dates'!N15:N35),MATCH(A4,'Students'!B15:B35,0))

Result is #N/A, though values are available in the date type and both respective cells.

Can you please help? Thanks.

Regards,

Tariq

Hello Tariq,

MIN function returns one value, so the second parameter of the INDEX function should by equal 1.

I have a large number of data in which I am trying to calculate weeks of supply. I am looking for a formula that will go to a specified cell (that contains my build for a specific category) then multiply last weeks sales by that build. If that does not equal 0, then I would like it to do the same thing and move on to the next cell and perform the same formula. Then continue until my result is zero, and finally count all the cells it performed this formula, giving me my weeks of supply.

Hello, Brian,

Your task needs a special solution. Sorry, we cannot help you with this.

Hi, I have an Index Match formula: =INDEX(AI1233:AU1234,MATCH(AH1196,AI1233:AU1233,0), MATCH(AI1223,AI1233:AI1234,0)) the AH1196 is a drop down list of Jan 2016-Dec 2016. The budget data it pulls looks like this:

Date Jan-16 Feb-16 Mar-16

Postage xxxx xxxx xxxx

When I have Jan-16 in the drop down field, it works great. When I change the drop down to any other month, I get #REF!. Not sure how to enable the use of the drop down in the formula.

Any help will be greatly appreciated.

What if I have a long time-series organized in by year (column 1) and month (column 2) and the value (column 3) and I want to compute seasonal averages (i.e. DJF, MAM, JJA, SON) over the entire time-series. How can I use the EXCEL average function to know to skip every 12th entry in generating the seasonal averages?

Hi Everyone. I am trying to get the min number from these set of value from Cell, D13 to D19.

With the method i use, it is going to be very tedious as i need to repeat it till D19.

Is there a way to rewrite this formula into an array where the formula will gather the number from D13-D19 and output the minimum?

=MIN(INDEX($D$2:$LK$3,MATCH($M$5,$B$2:$B$186,0),MATCH(D13,$D$1:$LK$1,0)),INDEX($D$2:$LK$3,MATCH($M$5,$B$2:$B$186,0),MATCH(D14,$D$1:$LK$1,0)))

Thank you!

I use offset =OFFSET($G$2,MATCH(M3,$G$3:$G$15,0),1) and I get #N/A for the id that didn't match is there a way to output 0(zero) or Null instead of #n/a?

I have this file:

data1 data2 data3 data4 default average new data

44.44 26.75 83.80 68.36 33.00 65.53 65.53

I need to use sumif to sum 3 of the five data rows omitting the the highest and the lowest values

data1 data2 data3 data4 default average new data

44.44 26.75 83.80 68.36 33.00 65.53 65.53

i have to use sumif to add 3 of the data rows, omitting the highest and the lowest values.

Thanks

Hi...can anyone help me use INDEX function to return a value and then drag it down to return values from other sheets in the same column ?

I am maintaining a speadsheet which which monitors materials delivery (detailed and summary).

I have 2 worksheets (sheet1 is for detailed report & sheet2 is for summary report) in a workbook. What I want is a formula to automatically write "Delivered" in the Summary Report if all items (2 items each PO number in the example shown) are "Delivered" in the Detailed Report; and write "Partial" if one is "Delivered" and the remaining is "Undelivered".

Both tables are Named Tables, so the number of items will increase in time.

I tried to search in the forums but failed to get what I'm looking for. Would appreciate any help.

Sheet1-Detail

PONo. Project ProjCode Vendor Material Status

1 Project 1 Prj001 ABC Material 1 Delivered

1 Project 1 Prj001 ABC Material 2 Undelivered

2 Project 1 Prj001 XYZ Material 3 Delivered

2 Project 1 Prj001 XYZ Material 4 Delivered

Sheet2-Summary

PONo. Project ProjCode Vendor Status

1 Project 1 Prj001 ABC Partial

2 Project 1 Prj001 XYZ Delivered

Good morning I am trying to write an INDEX MATCH Formula that will dynamically set the INDEX lookup column based on the header column.

what I have are 2 tabs which house my base data; we will call them Table1 and Table2. then I have a third tab which populates all its data based on INDEX MATCH Formulas, we can call this tab Lookup1.

I have made the table headers in Table1 and Table2 names ranges and in my Lookup1 tab, I want the table headers to be dropdowns from one of my dataset tables. I'd like to write the INDEX MATCH formula to change it's INDEX column if I changed the header title in my table.

is this possible without VBA? I have pasted the original formula below, the part colored in red is what I want to rewrite to reference the header of it's own column, match the column of the same name in the Table1 tab and insert that as the Index lookup. I believe I might need to nest in another INDEX and/or MATCH formula to do what I want, but I am not sure.

=IF([@[Employee ID]]"",INDEX(Table1[First Name],MATCH([@[Employee ID]],Table1[Employee ID]),0),"")

For those values that are not found - wrap your INDEX...MATCH functions in this - and the last entry can be your default value (if not found). I don't like the $REF or #N/A answers either

Forgot to mention the function - use IFERROR - wrap you INDEX...MATCH functions in IFERROR - and the last part of the function will be your default value (0 or "Not found - try again"). I don't like the #REF or #N/A answers either.

hello

The results show me the same which type it

the results:- =INDEX((A2:D3, A5:D7), 3, 4, 2)

Hello Yaseen,

This usually happens if a formula cell has a leading space or apostrophe before the equal sign; or if the Show Formulas mode is activated in the worksheet (Formulas tab > Formula Auditing). If neither is the case, please check out Excel formulas not calculating for other possible reasons and solutions.

Hello,

I am trying to sum multiple columns that have months has my headers. When we close on another month data is updated in that column. I want to automatically sum the data in the rows without continually every month updating my sum formula at the end. Can someone please show me how to do this with Index and Match Function? I want it to be dynamic!

Thanks,

I'm trying to reference a cell in another workbook without success. I think my best way forward is to use Indirect() but have also tried Index() without success.

It seems to be failing on the external file location as such:

The entire cell reference is as such"

"http://apollo.omega.dce-eir.net/contentserverdav/nodes/3161757/[BIASDetailedVRFtracker.xlsm]alignment!$D$12"

My Indirect call is as follows:

Indirect("'http://apollo.omega.dce-eir.net/contentserverdav/nodes/3161757/[BIASDetailedVRFtracker.xlsm]alignment'!$D$12")

It is failing on:

http://apollo.omega.dce-eir.net/contentserverdav/nodes/3161757/

Hi,

How to set ascending order date format with using formula to another column.

Example: all dated A1:A20

Ascending another column like B1:B20

Thanks & Regards,

Sri

I am trying to use the index function to display a dollar amount listed in a table in the month that it will be billed for. I have multiple projects and when the formula is dragged down to the next project, the index gets off because the projects have different start dates. Is there a better way to have the index start at the first billing month other than copying the formula from the previous project to the first billing month of the next project?

Thanks.

Hellow all,

I have two questions,please have a look and let me know is there anyway to find the solution,

1. I have two tables each having 37X12 rows and columns, First row and first column is having names for the corresponding data.Here the question is, i need to sort out column 1 data (which is having same names for two tables) from the reaming 35 columns of the two tables based on max, avg and min values. it is the combination of index, match for max min and avg, but no idea how to give the reference for two tables at a time to see max min and avg values for column data.

2. I have 15 matrices, each of the matrix is having 1833x1833 rows and columns. Here the data need to be sort out for specific rows and columns say for eg. between 500 to 1000. i need to sum these columns and rows by looking the sheet name and no idea how to specify the range to lookup and sum the data.

Looking for the solution,

Thanks, Subbareddy.

A B C D E F G H

1/1/16 ALEX SONY 2/1/16 JOHN DELL ? ?

A to C and D to E are consecutive data categories.

I want to return in Cell G1 the name of the Person who made the sale at the latest date (JOHN).

And return in Cell H1 the name of the Brand for which the sale was done (DELL).

Kindly help.

Date Name Brand Date Name Brand

are the entries.

I have a table with row 1 being dates from July thru to end of October and row 2 indicates whether a person is working (1) or not (0). I am trying to get my index/match formula to auto update to show how many days between today and the end of October the person is working. I have the following, but end up with #REF :-(

=sum(index(B4:CH4,2,match(today(),b4:ch4,0)):index(b4:ch4,2,85)

Thanks for any help

used a simpler approach, two SUMIFs

=sumif(B4:CH4,"="&Today(),B5:CH5)+sumif(B4:CH4,">"&Today(),B5:CH5)

gave me the answer I needed

I have to say your explanation and examples of the index function is the best I have seen.

Very clear and easy to follow.

Thank you so much!

Bruce

In Example 2 (Sum items between the specified two items)

you used the following formula

=SUM(INDEX(SourceData[Diameter],B1) : INDEX(SourceData[Diameter], B2))

is there a way i can add another condition? I want to only sum every second number in the range specified in the abovementioned formula?

so in your example 2, the answer will be 120,660+0+49,528+0 = 170,188

Thanks

Nicholas

Hi,

I would like to create a inventory file with condition as follow.

1) multiple products/items in one worksheet as follow

2) inventory use first in first out method to calculate col F,G,H.

Is there any formula in column F, G & H that can automatic calculate.

B C D E F G H

1 Description/Cost/QtyIn/QtyOut/QtyBal/BalAmt/QtyOutAmt

2 Product A 10 416 416 $4,160 $0

3 Product B 20 400 400 $8,000 $0

4 Product A 200 216 $2,160 200*10=$2,000 [E4*C2]

5 Product B 250 150 $3,000 250*20=$5,000 [E5*C3]

6 Product A 12 150 366 $3,960 $0

7 Product B 50 100 $2,000 50*20=$1,000 [E7*C3]

8 Product A 300 66 $792 (216*10)+(84*12)=$3,168

9 Product C 15 200 200 $3,000 $0

10 Product B 22 200 300 $6,400 $0

11 Product A 50 16 $192 $600

12 Product C 15 100 300 $4,500 $0

13 Product B 150 150 $3,300 $3,100

14 Product C 150 150 $2,250 $2,250

15 Product A 10 300 316 $3,192 $0

Hi Really struggling to source the correct formula required.

I have a table that has data entered including customer reference numbers and dates. the reference number can be entered several times in no particular order.

I want to lookup the reference number in one column, check that a date has been entered against all entries in another column and then return a Yes, No response.

Any help appreciated

I need some help with the formula (combination of Index and Match)- If I have row ass Quarters (B2:G2) and business listed in Column B- How can add total for business by Quarter by just changing the Quarter name in AI cell.

Hi,

=INDEX($E2:$E$300,MATCH("PE",$D2:$D$300,0))

=INDEX($E2:$E$300,MATCH("CE",$D2:$D$300,0))

I am using above 2 formulas to extract data from E2:E300 for matching two words like CE and PE, which is situated in column D2:D300. I want result for PE in column F2 and drag down. Another CE result want in column G2 and down. But the CE word starts from row above 100 in column E and when I drag it in column G2 and down I am getting incorrect match. But getting correct result for PE.

Perhaps you can help me out. I am using the following array formula {=INDEX(ASSETP,SMALL(IF(STATP=$A$10,ROW(ASSETP)MIN(ROW(ASSETP))+1),ROWS($B10:B$10)))}

to display vertically instances of asset #'s if they equal the status in cell A10. The formula works great. The cells in the "STATP" range are formulas to determine the status. =IF(K5="","L","X"). Later I realized that I needed to improve the status formula by checking for cost of the asset. I changed the status formulas to determine if cost was $500.

=IF(AND(K6="",H6>499.99),"L",IF(AND(K6="",H6<500),"L2","X"))

The status formula works fine, but my array formulas no longer work the way I expected. If I put the Status formulas back to original the array formulas work as expected.

SOLVED. When I changed the status formula one of the cells in the STATP range, gave an error as its result. This caused the array formulas to not work as expected. Once I corrected the error all array formulas worked as expected.

If I would like to sum the values of different individual columns based on row criteria then which formula should I use?

Example:

Name A1 Loc A2 Loc A1 Loc A2 Loc

ABC 5 7 8 5

XYZ 6 8 9 6

PQR 3 9 6 3

I want sum of XYZ values of A1 location

I am trying to retrieve the values from a range that match two criteria (A2, C2). I get only the first one, but I want them all in a dropdown list.

=INDEX(tbEmployee[Employee],MATCH($A$2&$C$2,INDEX(tbEmployee[Country]&tbEmployee[Category],,),0),0)

What shall I do?

Hi, Anastasia,

Try VLOOKUP function, it can return values based on few criteria.

Good day

i have a challenge . been working on a report card, but i can't go through :(

NAME ENG MATHS ADMA BIO GEO CIV PHY CHE AVG PSN

BRIAN 55 3 66 2 63 2 72 2 55 3 71 2 83 1 80 1 427 68 8

BKALUMO 65 2 52 3 0 FAIL 65 2 70 2 60 2 88 1 50 3 400 56 16

IAN 70 2 24 FAIL 36 FAIL 50 3 58 3 71 2 75 1 18 FAIL 360 50 22

TREVIS 88 1 54 3 51 3 80 1 70 2 77 1 90 1 72 2 477 73 3

GRIFFINS 68 2 26 FAIL 51 3 46 4 59 3 77 1 70 2 60 2 385 57 1 HARRISON58 3 12 FAIL 34 FAIL 54 3 55 3 71 2 75 1 40 4 353 50 25

ROSTEN 78 1 42 4 45 4 58 3 74 2 63 2 85 1 80 1 438 66 7

VICTOR 78 1 56 3 60 2 56 3 59 3 83 1 85 1 24 FAIL 421 63 9

GABRIEL 48 4 38 FAIL 33 FAIL 52 3 40 4 49 4 75 1 12 FAIL 302 43 28

The card works like this . a teacher will enter the mark for a pupil then it will calculate the grade ie 1-9(fail). and also calculates the best 6 subjects including english as shown in the 3rd last column.(SUM(LARGE(E3:Q3,{1,2,3,4,5}))+C3)

i want to now add a column that will calculate the points (sum of best grade). i have tried alot of formulars but the challenges comes because the grades where found by a formular and are not in a range (in different columns)

=SUM(SMALL(range,{1,2,3,4,5}))+M34 - this formular isnt giving an answer but an error ### #num!. am stuck on how to go about it. i noticed it could be because the grades for a particular pupil are in different columns and are a result of a formular.

Please help. Thanks in advance

Hello,

I have a list with 2 columns:

A:text

B:number

I wanted to

1- select records that have value 'USD'in column A (This outputs a range include recores that value of column A is 'USD')

2- Sum the column B values on range which specified in previuse step.

HOW TO DO THIS IN EXCEL USING FUNCTIONS?

thanks before

Hello,

I have two columns (A and B) and that they are reference columns. I need to get the columns (C and D) are sorted according to the ref. column B using INDEX and/or MATCH functions. For instance:

No. B C D

= = = =

1 A 2 4

2 C 1 2

3 E 3 1

4 C 4 3

After sorting according to column B the columns C and D it becomes below:

No. B C D

== = = =

1 A C E

2 C A C

3 E E C

4 B C A

How to do this using functions (not normal or custom sorting) in Excel?

Thank you so much in advance.

Hi There

I have an employee data base, i have to send every week the manpower list each dept.

i want to create the drop down list by dept. when i select any dept my worksheet should the employees names in that dept.

thanks your help

Estimated. Sq. Ft Estimated Capex Spend (£)

2,000 185,000

3,500 436,000

5,000 660,000

how do i find whats estimated capex spend for example 2500 sq ft based on the data above. what formula could i use to get an estimate?

I have a schedule for 10 teams playing on 5 boards. They play 3o days and play 2 games each day, against different teams.

Left column are the dates, then to the right is what teams play each other and the board they play on above the teams.

I would post the schedule but do not see where I can post it.

I presently have a chart that I populate manually and want to do automatically depending upon what the date is, whether the 1st or 2nd game is being played.

I tried index, match, vlookup and cannot get any of them to work with the schedule I have.

What do you suggest I do? Hopefully I explained what I'm trying to do.

Thanks,

Jim

I am building a task list that needs to populate a "dashboard". There is a row in each task labeled "launch" that has a date associated with in the neighboring column. Then in the same column as "launch" there are a list of activities that has a list of corresponding "date completed". The dashboard has columns that match the activity title and the rows have the matching campaign. I'm currently using this function: =IF(ISERROR(INDEX(ALLCAMPS,SMALL(IF(ACTIVITY=$R$1,ROW(Complete_Date)),ROW(1:1)),12)),"",INDEX(ALLCAMPS,SMALL(IF(ACTIVITY=$R$1,ROW(Complete_Date)),ROW(1:1)),12)) with delete + shift + enter. It logs down my system. Is there a better way to do?

Hello,

If I’ve understood your task correctly, please try to use the modified formula below:

=IFERROR(INDEX(ALLCAMPS,SMALL(IF(ACTIVITY=$R$1,ROW(Complete_Date)),ROW(1:1)),12),"")

If this doesn’t work either, I’m afraid you’ll need to use a special macro then. We can’t help you with this since we do not cover the programming area (VBA-related questions). In this case please try to find the solution in VBA sections on mrexcel.com or excelforum.com.

I do hope you’ll manage to solve this task!

Am struggling to understand how to construct a formula that populates the "grades" column below by reading the "rounded" column content and returning the value in the "Grade" column if the value in the "rounded" column is equal to or greater than the value in the "From" and is equal to or less than the value in the "To" columns.

Any help appreciated:

rounded grades

165,000 ?

120,000 ?

110,000 ?

100,000 ?

100,000 ?

90,000 ?

90,000 ?

88,580 ?

85,000 ?

80,000 ?

From To Grade

160,000 170,000 A1

150,000 159,999 A2

140,000 149,999 A3

130,000 139,999 A4

120,000 129,999 A5

110,000 119,999 A6

100,000 109,999 A7

90,000 99,999 A8

85,000 89,999 B1

80,000 84,999 B2

75,000 79,999 B3

70,000 74,999 B4

65,000 69,999 B5

how can i use index formula. in multiple table in one sheet

Hi,

I want to write a VBA function making use of LINEST excel function to calculate some tstats for the slope. I know that i have to combine the INDEX function with the LINEST function, but i don't know how. Can anyone help?

Hi,

I want to write a VBA function making use of LINEST excel function to calculate some tstats for the slope. I know that i have to combine the INDEX function with the LINEST function, but i don't know how. Can anyone help?

Index match on multiple criteria with multiple sheets

i have a workbook with 4 sheets 1st three are Jan, Feb, Mar and 4th one is home. Columns are style no., qty and unit price. i want to check index match the unit price of mentioned style number from all three sheets. Like i want a formula in HOME sheet in cell C2 which see style no. in cell A2 and then check it's unit price in all three sheets and put unit price of matched style no.

Three sheets Jan, Feb, Mar are like this.

Style No. Qty Unit Price

WTC123456 456 45,000

WTB281654 1000 65,000

HOME Sheet.

Style No. Qty Unit Price

WTC123456 456 here i want formula

WTB281654 1000

because some styles are put in Jan and some are in Feb so on. but home sheet has all the styles no.

so actually i have 12 sheets of 12 months

Thanks

Hi,

can we use INDEX to select the default value of a list in data validation?

for example in below list

A1 Select one

A2 AA

A3 BB

A4 CC

by default, the drop list in D1 which is created by data validation >list

shows select one/

Hi,

I have seen videos to populate pivot based on the value selection in the drop down using Index and Match function. My data is tricky not able to replicate the same issue. Below are the columns of info I have in the spread sheet:

Dept Month Target Actual Difference

Mktg Jan-18 100 80 20

Mktg Feb-18 120 118 2

Mktg Mar-18 90 94 4

TeleM Jan-18 200 210 10

TeleM Feb-18 150 148 2

TeleM Mar-18 110 103 7

I want my chart to change dynamically. Could you please help how to write the Index Formula. I want to display the chart data by for selected period by dept. I will select the dept and then the range of the periods which will display the chart. Since I have multiple times dept and month are coming in the data spreadsheet it is not giving the right result.

Hi,

Could you pls show me how to find the median value of below case:

Q1: How to find the Median of STAFF & SUPVR grade staff's salary?

Q2: How to find the Median of MGR & ADIR grade staff's salary?

A B

1 STAFF 100

2 SUPVR 120

3 ADIR 500

4 STAFF 150

5 MGR 200

I could make it work by using below formula...

=MEDIAN(IF(A1:A5="STAFF",IF(A1:A5="SUPVR",B1:B5)))

=MEDIAN(IF((A1:A5="STAFF")*(A1:A5="SUPVR"),B1:B5))

Thank you for the support.

Suzanne:

Where the employee titles are in G3:G8 and the salaries are in H3:H8 enter this in H10:

{=MEDIAN(IF(G3:G8=G10,H3:H8))}

Then in G10 enter one of the employee titles and the median salary for that title should be displayed in H10.

Note the curly brackets around the formula. This indicates to Excel that this is an array. After you enter the formula in the formula bar, put the cursor in the formula and click CTRL Shift Enter and you should see the curly brackets around the entire formula. When entered in this manner Excel will treat the formula as an array.

You can change the addresses and ranges to suit your needs, but when you change something in the formula you need to put the cursor in the formula and click CTRL Shift Enter again to get the array back.

Hi,

I have a workbook with roughly 40 identical sheets of data. I use the following formula to draw this data to a dashboard dependent on the sheet name.

=+SUM(INDIRECT("'"&B2&"'!$H$130:$H$141"))

How can I change the formula to automatically update if a row is inserted above the data that is being referenced? i.e the data set effectively moves down by 1 so that formula would have to read as:

=+SUM(INDIRECT("'"&B2&"'!$H$131:$H$142"))

Hi, I have the following table:

10/6/18 11/6/18 12/6/18 13/6/18

jan 5 3 2

tom 3 8 1

anna 1 1 1

I would like to get one formula where I can look up a name than find the n-number in that row and return the date correspondent with that n-number.

eg. find anna, 2 number = 11/6/18

or find tom, 1 number = 11/6/18

or find tom, 3 number = 13/6/18

Please can someone help me.

thanks

Hi,

I have the following table ("x" represent blank cell)

date: 10/6/18 11/6/18 12/6/18 13/6/18

Jan 5 x 3 2

Tom x 3 8 1

Anna 1 1 x 1

I would like to get one formula where I can look up a name than find the n-number in that row and return the date correspondent with that n-number.

eg. find anna, 2 number = 11/6/18

or find tom, 1 number = 11/6/18

or find tom, 3 number = 13/6/18

Please can someone help me.

thanks

Hello, I hope you are still following these responses. I'm trying to process some autosomal DNA results, by identifying where segments are matching against the full results table 720450 rows long, from a table telling me the Chromosome and the start and stop locations of the matching sections. This identification process is to be used in a process called phasing where I need to extract actual results to form the basis of reconstructed kits. I can do this process manually by writing a 0 against all non matching sections, and in the same column 1 for the rows within each matching section. I have used the INDEX (.. Match... ) ) combination before but I can't work out how to proceed in this case. The [Segmeant ID] column is where I want the out come to be recorded. In words - For the matching segments in chromosomes 1-22 each with a start and end location, look up in the [position] column (720450 rows long) and write a [1] in the [Segment ID] column for all locations within a matching segment and [0] when there is no matching. I have hidden other columns with other relevant information, so as not to confuse the question. I though I could use a simplified IF and AND functions, but I now realized this is a better approach. I could send an Excel sheet if that helps understanding the requirement.

RSID CHR POSITION RESULT Segment ID Chromosome Start Location End Location

rs4477212 1 82,154 -- 0 1 15,359,967 41,402,143

rs3094315 1 752,566 -- 0 1 50,056,542 153,312,871

rs3131972 1 752,721 -- 0 1 163,093,382 201,166,817

rs12562034 1 768,448 -- 0 1 214,057,330 237,565,188

rs12124819 1 776,546 -- 0 2 11,944 220,002,834

rs11240777 1 798,959 -- 0 3 122,014,566 192,758,191

rs6681049 1 800,007 -- 0 4 63,814,811 91,516,422

rs4970383 1 838,555 -- 0 4 158,857,135 190,937,862

rs4475691 1 846,808 -- 0 5 19,760,858 107,011,958

rs7537756 1 854,250 -- 0 5 147,717,357 180,236,547

rs13302982 1 861,808 -- 0 6 155,815 27,216,699

rs1110052 1 873,558 -- 0 6 114,752,803 170,919,470

rs2272756 1 882,033 -- 0 7 43,748 8,714,115

rs17160698 1 887,162 -- 0 7 18,831,265 129,380,647

rs3748597 1 888,659 -- 0 7 148,038,351 155,812,421

rs13303106 1 891,945 -- 0 8 164,984 40,954,561

rs28415373 1 893,981 -- 0 8 63,335,905 146,299,857

rs13303010 1 894,573 -- 0 9 46,587 4,501,492

rs6696281 1 903,104 -- 0 9 80,127,963 104,550,554

rs28391282 1 904,165 -- 0 10 95,844 8,855,244

rs2340592 1 910,935 -- 0 10 108,879,609 134,066,136

rs13303118 1 918,384 -- 0 11 194,062 25,120,833

rs2341354 1 918,573 -- 0 11 80,789,029 134,945,120

rs6665000 1 924,898 -- 0 12 86,300 76,348,112

rs2341362 1 927,309 -- 0 12 130,093,883 133,838,353

rs9777703 1 928,836 -- 0 13 19,020,095 115,106,996

rs1891910 1 932,457 -- 0 14 32,733,284 88,510,255

rs9697457 1 934,345 -- 0 15 20,004,966 29,581,108

rs35940137 1 940,203 -- 0 16 1,079,531 85,959,438

rs3128117 1 944,564 -- 0 17 19,519,444 43,147,351

rs2465126 1 947,034 -- 0 17 58,240,764 81,044,305

rs2341365 1 948,692 -- 0 19 4,458,063 46,367,703

rs15842 1 948,921 -- 0 21 9,922,018 25,935,761

rs6657048 1 957,640 -- 0 22 16,055,122 51,214,796

I'm using and index function to sort names into different sheets based on one master sheet that has drop downs. For example: all the names will be on one sheet and then there will be a drop down list for their group and their name will go that corresponding sheet. My issue right now is that it includes all groups below it. So the "Red" group will have red, blue and yellow in the sheet. The blue group will have blue and yellow in the sheet. How do I have it stop with only the specific color?

=IFERROR(INDEX(Personnel!$A4:$A$400, MATCH(0, IF("Blue"=Personnel!$B$4:$B$400, COUNTIF($CW$1:$CW1, Personnel!$A4:$A$400),""),0)),"")

Data Table (Location and Holiday Dates)

Loctn Holidays

SPD 01-Jan-19

SPC 01-Jan-19

SPM 01-Jan-19

SPK 01-Jan-19

SPB 01-Jan-19

SPD 14-Jan-19

SPC 14-Jan-19

SPM 14-Jan-19

SPK 14-Jan-19

SPC 15-Jan-19

SPB 15-Jan-19

Working sheet, need the days between the two dates.

Loctn Start Date End Date No of Days No of Days

SPD 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 25.00

SPC 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 24.00

SPM 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 25.00

SPK 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 25.00

SPB 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 25.00

i need to call cells from another tab

my reference cell consist of a name Jhon

but there are multiple results for jhon & I want to call all of them in a single tab, please mention the formula i could use.

Colum-1, Column-2

eg: John-----4458

john-----4459

What will the following formula return?

=INDEX(A5:H448,5,6)

Hi can you help me with my problem here.. it seems my index match formula is correct but I can't make it work somehow.. I have 3 criteria's to consider to get the correct data I need.

It works if I only use 1 match with the index, but I have to use two more match formula to consider the other two criterias....

Hello;

My problem is either very simple or not possible. But, I could not find solution.

I have data like :

1,one,2,two,3,three,4,four,5,five etc.....

When i put these data to cells, I am achieving my target through vlookup, Match, etc.

I want to finish whole formula in 1 cell, without referring any other cell. i.e. These data should come as constant.

OR,

Please tell me how can i increase number of content of formula in a cell from 8192 to 12000 ???

Thank you,

Best Regards,

I have a large sheet and want to calculate the attendance of each student for a specific period. For example, 1=present and 0=absence, I want to sum each student present from date10 to date80. How?

Date : date1, date2, date3, date4 ... date99, date100

student1, 1,1,0,1 ... 1,1

student2, 1,0,1,0 ... 0,1