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

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

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

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

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

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

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

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

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

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



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)