Description
Returns a value or reference based on its row and column position within that array or reference.
NOTES
- A reference can be made up of non-adjacent selections.
- If only row_num or column_num is used, INDEX returns an array of all rows or columns in the array.
- On versions of Excel that do not support dynamic arrays, if the formula used is an array formula you will need to press CTRL+SHIFT+ENTER to confirm formula.
Syntax
Copied!//𝘢𝘳𝘳𝘢𝘺 𝘧𝘰𝘳𝘮. =INDEX(array, row_num, [column_num]) //𝘳𝘦𝘧𝘦𝘳𝘦𝘯𝘤𝘦 𝘧𝘰𝘳𝘮. =INDEX(reference, row_num, [column_num], [area_num])
SAMPLE data
| | A | B | C | D | E | F |
|:---:|:----|:--------|------:|------:|:----|:----|
| 1 | | | | | | |
|-----|-----|---------|-------|-------|-----|-----|
| 2 | | Fruit | Price | Count | | |
|-----|-----|---------|-------|-------|-----|-----|
| 3 | | Apples | $0.69 | 40 | | |
|-----|-----|---------|-------|-------|-----|-----|
| 4 | | Bananas | $0.34 | 38 | | |
|-----|-----|---------|-------|-------|-----|-----|
| 5 | | Lemons | $0.55 | 15 | | |
|-----|-----|---------|-------|-------|-----|-----|
| 6 | | Oranges | $0.25 | 25 | | |
|-----|-----|---------|-------|-------|-----|-----|
| 7 | | Pears | $0.59 | 40 | | |
|-----|-----|---------|-------|-------|-----|-----|
| 8 | | Almonds | $2.80 | 10 | | |
|-----|-----|---------|-------|-------|-----|-----|
| 9 | | Cashews | $3.55 | 16 | | |
|-----|-----|---------|-------|-------|-----|-----|
| 10 | | Peanuts | $1.25 | 20 | | |
|-----|-----|---------|-------|-------|-----|-----|
| 11 | | Walnuts | $1.75 | 12 | | |
|-----|-----|---------|-------|-------|-----|-----|
| 12 | | | | | | |EXAMPLE 1
Copied!//𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭, 𝘤𝘰𝘭𝘶𝘮𝘯_𝘯𝘶𝘮=0 [0,1,2..], 𝘢𝘳𝘦𝘢_𝘯𝘶𝘮=1 [1,2,..] //𝘤𝘢𝘭𝘤𝘶𝘭𝘢𝘵𝘦 𝘵𝘩𝘦 𝘵𝘰𝘵𝘢𝘭 𝘲𝘶𝘢𝘯𝘵𝘪𝘵𝘺 𝘰𝘧 𝘖𝘳𝘢𝘯𝘨𝘦𝘴 𝘢𝘯𝘥 𝘗𝘦𝘢𝘳𝘴. =SUM(INDEX(D3:D11, MATCH({"Oranges", "Pears"}, B3:B11, 0))) -> 𝘳𝘦𝘵𝘶𝘳𝘯 65
example 2
Copied!//𝘳𝘦𝘵𝘶𝘳𝘯𝘴 𝘢𝘯 𝘢𝘳𝘳𝘢𝘺 𝘰𝘧 5 𝘤𝘰𝘯𝘴𝘦𝘤𝘶𝘵𝘪𝘷𝘦 𝘯𝘢𝘵𝘶𝘳𝘢𝘭 𝘯𝘶𝘮𝘣𝘦𝘳𝘴 𝘧𝘳𝘰𝘮 1 𝘵𝘰 5 (≤ 𝘌𝘹𝘤𝘦𝘭2021). =ROW(A1:INDEX(A1:A11, 5))