About this lesson
INDEX: pulling audit samples by row number in Google Sheets
In Google Sheets, INDEX returns the value at a given position in a range: =INDEX(A2:A11,3) is the third value in A2:A11, and =INDEX(A2:C11,3,3) is row 3, column 3 of the table. The newer XLOOKUP function hasn't replaced INDEX on its own. It replaced INDEX paired with MATCH, the usual way to find a value from a label such as a ticker or a store name.
Using Excel? Do this lesson in Excel.
=INDEX($A$2:$A$11,B14)What you'll practise
Pull the invoices an audit sample picked by their row numbers, use INDEX's row and column form, and find the latest entry in a list.
3 steps · 30 points · the cells outlined in gold below
- INDEX
- row and column numbers
- last item in a list
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Customer | Amount ($) | |
| 2 | INV-40817 | Tasman Travel Co | $4,862.50 | |
| 3 | INV-40823 | Southern Cross Events | $12,415.00 | |
| 4 | INV-40829 | Bellarine Weddings | $7,931.80 | |
| 5 | INV-40836 | Otway Foods | $2,284.35 | |
| 6 | INV-40841 | Murray River Tours | $18,650.00 | |
| 7 | INV-40847 | Gippsland Schools Camp | $5,377.20 | |
| 8 | INV-40852 | Tasman Travel Co | $9,104.65 | |
| 9 | INV-40858 | Kimberley Cement | $3,618.90 | |
| 10 | INV-40863 | Coastline Conferences | $21,940.00 | |
| 11 | INV-40871 | Southern Cross Events | $6,452.75 | |
| 12 | ||||
| 13 | Sample | Row in list | Invoice | Amount ($) |
| 14 | 1 | 3 | ||
| 15 | 2 | 7 | ||
| 16 | 3 | 9 | ||
| 17 | ||||
| 18 | Latest invoice |