WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to use index in excel

August 3, 2022
2.8K Views

How to use index in excel? You can use it to refer data from a location and return it from within a range or table. You can use it to highlight or mention any specific individual or group of cells to other location or even other worksheets.

This step-by-step guide will explain how to use index in excel in your project. There will be two methods to use the function, one will explain via shortcut key and the other is by clicking the option.

Use Index function by shortcut

This is how you can instanly access the Index function:

1. Open a file you wish to use index function. This is a test file which contain names of members with height and weight data.

2. Now click on the cell you want to use Index function.

3. Now type “equals” sign and write ”index”. Once you do it, you will notice the index function appears in the dialogue box.

4. Click Tab button on keyboard. The index fucntion is now active and requires some input. the Row_num requires specify the row, the Array function requires to identify the area of cells under consideration, and the Column_num requires to input the column.

5. Let’s say we want to index Kevin Weight which is 175. Move the cursor at the corner of the table and drag diagonally highlighting all table cells from F8 to H14.

6. Now put comma “,” to separate the function values. Enter row number as 4 as below because name Kevin is placed in 4th row starting from “Name” title.

7. Again insert comma “,” and now enter column number as 3 as below. It is because the Kevin’s weight data is in 3rd column from the “Name” title.

8. Now press Enter key. Notice the Kevin’s weight is indexed as 175 because Excel referenced 4th row and 3rd column within the specified cells.

Use Index function by clicking index option

If you want to specifically find and click the Index option. Here your guide to do so:

1. Open a file you wish to use index function. This is a test file which contain names of members with height and weight data.

2. Now click on the cell you want to use Index function.

3. Head to Formula tab and click it. This opens various options in the ribbon.

4. Now click on Lookup and Reference. This opens a drop-down menu.

5. Now click on Index option.

6. Once clicked, you will notice a dialogue box appears with many fields.

7. Now the procedure of entering the data is same as mentioned previously.Click on the button right next to the Array field.

8. Now select the cells between F8 to H14 just like before. Notice the selected area is appears on the dialogue box.

9. Hit Enter key and the cells are selected.

10. Now in the Row_num, type 4 and Column_num type 3.

11. Press Enter key. Again the same answer appears as 175 which is exactly the Kevin’s weight.

So this is how to use index in excel in your worksheets to refer any important data to other places. It will always refer to the same cell you assign to the function.

Need to edit Word/Excel/PPT file free of charge? Download WPS Office edit files like without any cost. Download now! to get enjoyable working experience.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.