Excel Toolkit / Function guides
INDEX in Excel: explanation and examples
Return a value from a range by its row and column position. Start with one direct calculation.
These examples use the Moon Energy teaching case. All businesses and data are fictional. They are learning examples, not financial or tax advice.
Microsoft’s definition
The INDEX function returns a value or the reference to a value from within a table or range.
Read the INDEX function reference on Microsoft Support
Three ways to understand INDEX
Transactional finance
Moon Energy’s finance team is reviewing opening-period supplier bill amounts and a comparison budget.
Return the actual at the editable row number in E2.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Site code | Site type | Bill net GBP | Budget | Selected row |
| 2 | S01 | Hospital | £32,439.68 | £29,195.71 | 2 |
| 3 | S02 | Office | £7,420.00 | £6,678.00 | |
| 4 | S03 | Office | £6,128.42 | £5,515.58 | |
| 5 | S04 | Retail | £3,216.42 | £2,894.78 | |
| 6 | S05 | Retail | £3,771.49 | £3,394.34 |
Formula: =INDEX(C2:C6,E2)
Result for this sample: £7,420.00
Business operations
Moon Energy’s operations team is reviewing site electricity readings and a comparison consumption budget.
Return the actual at the editable row number in E2.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Site code | Site type | Electricity kWh | Budget | Selected row |
| 2 | S01 | Hospital | 143695 | 129325.5 | 2 |
| 3 | S02 | Office | 32000 | 28800 | |
| 4 | S03 | Office | 26234 | 23610.6 | |
| 5 | S04 | Retail | 13234 | 11910.6 | |
| 6 | S05 | Retail | 15712 | 14140.8 |
Formula: =INDEX(C2:C6,E2)
Result for this sample: 32,000
Abstract
Moon Energy’s orbital stations hold photon-cell reserves. Their controller compares current reserves with planned reserves.
Return the actual at the editable row number in E2.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Station code | Station class | Photon cells | Budget | Selected row |
| 2 | S01 | Hospital | 14500 | 13050 | 2 |
| 3 | S02 | Office | 3200 | 2880 | |
| 4 | S03 | Office | 2600 | 2340 | |
| 5 | S04 | Retail | 1300 | 1170 | |
| 6 | S05 | Retail | 1600 | 1440 |
Formula: =INDEX(C2:C6,E2)
Result for this sample: 3,200
Experiment in Formula Lab
Open INDEX in the interactive Formula Lab →
The practice engine supports a focused subset of Excel behavior. Lookups are exact-match only; it does not support all Excel functions, dates, arrays or workbook imports. Read the limits shown in Formula Lab before interpreting a result.