Comments on: INDEX function in Excel - 6 most efficient uses

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

Comments page 2. Total comments: 124

  1. 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

  2. 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,

  3. 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.

    1. 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....

  4. What will the following formula return?

    =INDEX(A5:H448,5,6)

  5. 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

  6. 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

  7. 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)),"")

  8. 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

  9. 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

  10. 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"))

  11. 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.

    1. 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.

  12. 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.

  13. 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/

  14. 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

  15. 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?

  16. 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?

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

  18. 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

  19. 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?

    1. 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!

  20. 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

  21. 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?

  22. 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

  23. 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.

  24. 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

  25. 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

  26. 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?

  27. 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

  28. 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.

    1. 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.

  29. 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.

  30. 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.

  31. 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

  32. 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

  33. 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,670+0+49,528+0 = 170,188

    Thanks
    Nicholas

  34. 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

  35. used a simpler approach, two SUMIFs

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

    gave me the answer I needed

  36. 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

  37. Date Name Brand Date Name Brand
    are the entries.

  38. 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.

  39. 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.

  40. 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.

  41. 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

  42. 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/

  43. 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,

  44. hello

    The results show me the same which type it
    the results:- =INDEX((A2:D3, A5:D7), 3, 4, 2)

    1. 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.

  45. 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.

  46. 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

  47. 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),"")

  48. 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

  49. 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 ?

  50. 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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)