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 3. Total comments: 124

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  15. used a simpler approach, two SUMIFs

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

    gave me the answer I needed

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

  17. Date Name Brand Date Name Brand
    are the entries.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    1. Hello, Brian,

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

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

    1. Hello Tariq,

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

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

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

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

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

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

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

    1. Hello Sid,
      Try use a named range with a few rows.
      As sample: =INDEX(FirstBook.xlsx!ANYRANGE,6)

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 :)