Lookup
INDEX function in Excel:syntax and examples
INDEX returns the value at a given row and column of a range. In Spanish Excel it is called INDICE. Syntax: =INDEX(array, row_num, [column_num]).
Syntax
=INDEX(array, row_num, [column_num])In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
array | Required. The range you pull the value from. Example: A2:D6. |
row_num | Required. The row inside that range, counting from 1 (the first row of the range, not row 1 of the sheet). Example: 4. |
column_num | Optional. The column inside the range, counting from 1. If the range has a single column, you do not need it. Example: 2. |
How do you use INDEX? A step-by-step example
INDEX is like giving coordinates: you tell it the range, the row and the column, and it returns what sits at that crossing. It is the same catalog as in VLOOKUP:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Product | Category | Price |
| 2 | P-101 | Stapler everyone borrows | Office | 4.95 |
| 3 | P-102 | A5 notebook | Stationery | 2.30 |
| 4 | P-103 | Markers (12 colors) | Stationery | 6.80 |
| 5 | P-104 | Office chair | Furniture | 89.00 |
| 6 | P-105 | Desk lamp | Furniture | 24.50 |
=INDEX(A2:D6,4,2) returns Office chair.
A2:D6is the range, without the headings.4is the fourth row of the range. The range starts in row 2 of the sheet, so it is row 5.2is the second column of the range: Product.
If the range has a single column, the row is enough: =INDEX(D2:D6,4) returns 89.
What else can you do with INDEX?
| You want | Formula | Result |
|---|---|---|
| The first value of a column | =INDEX(B2:B6,1) | Stapler everyone borrows |
| The last value of a column with no gaps | =INDEX(D2:D6,COUNTA(D2:D6)) | 24.5 |
| A value from a table of months (row 2, column 2) | =INDEX(L2:N3,2,2) | 5 |
| Add a whole row (column 0) | =SUM(INDEX(L2:N3,2,0)) | 10 |
| The price of the code MATCH finds | =INDEX(A2:D6,MATCH("P-102",A2:A6,0),4) | 2.3 |
The months table in the examples is this:
| K | L | M | N | |
|---|---|---|---|---|
| 1 | Jan | Feb | Mar | |
| 2 | P-101 | 30 | 42 | 25 |
| 3 | P-104 | 3 | 5 | 2 |
The last row of the examples table is the real use of INDEX: you almost never type the position by hand, MATCH works it out. The complete lookup, step by step, is in INDEX and MATCH.
INDEX or VLOOKUP?
They do not compete: INDEX does not look anything up, it only returns what is at a position. The real comparison is INDEX + MATCH against VLOOKUP.
| INDEX alone | INDEX + MATCH | VLOOKUP | |
|---|---|---|---|
| What you give it | A position | A value to look up | A value to look up |
| Looks to the left | Does not look up | Yes | No |
| Versions | All | All | All |
| When you would use it | Last value, a whole row, coordinates you already know | Lookups in any direction | Plain lookups to the right |
How does INDEX show up in an Excel test?
In a job interview INDEX almost always appears with MATCH: "Bring in the code from the product name." If you see it alone, it is usually for "the last recorded value" of a column, as in the table above.
In a skills test:
What does
=INDEX(A2:D6,2,3)return? a) The value of cell B3 · b) The value of cell C3 · c) The value of cell C2 · d) #REF!
Answer: b. Row 2 of the range A2:D6 is row 3 of the sheet, and column 3 is C. To count how many values a column has before you point INDEX at the last one, see COUNTA.
Practice INDEX
The English exercises are on the way. Until then, copy the catalog and the months table into a blank sheet, try each formula in the table, and then change the row number to 6 to see #REF! appear.
Available in: all versions of Excel. Source: Microsoft: INDEX function. Results calculated with Formulo's engine.
Common mistakes
- #REF!: the position is outside the range
- A2:D6 has 5 rows and 4 columns. =INDEX(A2:D6,6,1) asks for row 6 and =INDEX(A2:D6,2,5) asks for column 5: both give #REF!. Widen the range or check the number.
- #VALUE!: the position is negative or text
- =INDEX(D2:D6,-1) gives #VALUE!. It happens when the row number comes from a calculation that goes negative.
- It brings back a value one row too high or too low
- You count the rows from row 1 of the sheet instead of from the start of the range. In A2:D6, row 4 of the range is row 5 of the sheet.
Frequently asked questions
What is INDEX called in Spanish?
INDICE, with no accent mark. In a Spanish Excel: =INDICE(A2:D6;4;2).
What is INDEX for if I already know the position?
On its own, not much. Its main use is with MATCH, which works out the position: together they make a lookup that works to the left and in every version of Excel.
How do I get the last value of a column with INDEX?
Use the count of values as the row: =INDEX(D2:D6,COUNTA(D2:D6)) returns 24.5, the last price. It works if the column has no gaps.
What does a 0 in row_num or column_num do?
It returns the whole row or column. =SUM(INDEX(L2:N3,2,0)) adds the second row of the range and returns 10.
Does INDEX count from the sheet or from the range?
From the range. In A2:D6, row 1 is row 2 of the sheet. That is the most common reason INDEX brings back a value one row off.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.