Index function in Excel - how it works

Excel offers an index function to call up data from tables and subtables. We explain how you can use this function.

Use index function in Excel

Excel is a spreadsheet program. You can call up individual entries in a table using the combination of row and column. You can create multiple sub-tables in a table. You can query values ​​from these with the index function. We use an example from Blumenhandel GbR to explain how this works.

1. Blumenhandel GbR has an Excel document with the "Statistics" table and the "Working hours" (red) sub-table.
2. The list of employees forms the rows of the subtable, a list of days of the week the columns. The hours worked per employee and day (yellow) are entered.
3. You can use the index command to find out which employee worked on which day of the week and for how long: "= INDEX (table; row; column)", ie "= INDEX (working hours; employee list; weekday list)".

The comparison function

The index function is particularly useful in combination with the comparison function. This can relieve you of the counting of the desired row and column.

1. Enter the name of the employee you want in a cell. Alternatively, you can also select the employee name using a dropdown menu.
2. In another cell, you can have the row determined which corresponds to the row in the subtable. To do this, enter "= COMPARISON (employee; employee list; 0)". The line number corresponding to the name is output (yellow).
3. Do the same with the day of the week and column number (green).
4. If you now use the index function, you can read in the numbers output using the comparison function (blue).

Further CHIP practical tips on Excel explain how you can determine the highest number from a table, fix areas or draw frames around tables and offer Excel exercises with solutions.