Excel Function Translator Logo
    The Excel Translator

    INDEX

    Lookup & Reference

    Returns the value of a cell in a table based on the row and column numbers you specify.

    Translations
    EnglishINDEX
    FrenchINDEX
    SpanishINDICE
    GermanINDEX
    ItalianINDICE
    PortugueseÍNDICE
    DutchINDEX
    PolishINDEKS
    RussianИНДЕКС
    TurkishİNDİS
    CzechINDEX
    HungarianINDEX
    SwedishINDEX
    DanishINDEKS
    FinnishINDEKSI
    Syntax
    INDEX(array, row_num, [column_num])

    Arguments

    • arrayThe range of cells containing your data
    • row_numThe row number in the array to return
    • column_numThe column number in the array to return(optional)
    Examples
    =INDEX(A1:C10,5,2)
    Cell value

    Returns the value in row 5, column 2 of the range

    =INDEX(A:A,10)
    A10 value

    Returns the 10th value in column A

    =INDEX(A1:D1,3)
    C1 value

    Returns the 3rd value in a single row

    Tips & Best Practices
    • •INDEX+MATCH is more flexible than VLOOKUP (can look left)
    • •If row_num or column_num is 0, returns entire column or row
    • •Great for dynamic ranges and two-way lookups
    Common Mistakes
    • •Hard-coding the row number instead of pairing INDEX with MATCH to find it dynamically. It works until the data moves, then it quietly returns the wrong thing
    • •Swapping row_num and column_num by accident, which pulls a value from a completely different cell
    • •Adding a column_num on a single-column array when it's not needed, which throws a #REF! error for no obvious reason
    Related Functions
    MATCHFinds the position INDEX needs to do its job - you'll rarely see one without the other
    VLOOKUPQuicker to write for a simple left-to-right lookup, but it can't look left the way INDEX/MATCH can
    XLOOKUPThe newer function that covers most INDEX/MATCH use cases in one simpler formula
    Frequently Asked Questions

    Why bother with INDEX and MATCH instead of just using VLOOKUP?

    Because INDEX/MATCH doesn't care which side of your lookup column the answer is on - it can look up a value anywhere and pull the result from anywhere else. It also tends to run faster on big datasets and won't break the moment someone inserts a column.

    What does INDEX return if row_num is 0?

    The entire column pointed to by column_num (or the entire row, if you set column_num to 0 instead). Handy inside array formulas or when you need to hand a whole column to another function.

    Can INDEX pull a value out of a full table, not just one row or column?

    Yes - give it both row_num and column_num, like INDEX(A1:D10,5,2), and it drops you right on that one cell.

    Need to translate a formula using INDEX?

    Use our translator to convert your complete formula