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 function in excel (In 20 Seconds)

July 26, 2022
2.9K Views

Impress your boss by using Excel's INDEX and MATCH functions. INDEX and MATCH should be used in place of VLOOKUP. You'll need INDEX and MATCH to conduct sophisticated lookups.The Excel Lookup and Reference functions section includes the INDEX Function. A range or array's value at the specified location will be returned by the function. The MATCH function is frequently combined with the INDEX function. We can describe it as an alternative method of performing VLOOKUP.In addition to looking up a value in a list or table, INDEX as a financial analyst can be utilized in other types of analysis. It can be used in financial analysis for search and to return a column's sum combined with other methods.The INDEX function is available in two formats:The array format.The reference format.When we want to return the value of a specific cell or array of cells, we utilize the array format and when we want to return the reference of the cell at the intersection of row num and col num, we utilize the reference format.Match in excel online, 2016 and 2019:The position of a value within a specified range is returned by the MATCH function. The MATCH function below, for instance, searches for the value 53 in the range B3:B9.

Explanation: Position 5 in the range B3:B9 contains 53 (first argument) (second argument). To return an exact match in this example, we use the MATCH function, therefore we set the third argument to 0.Index in excel:In a one-dimensional space, the INDEX function below returns a specified value.

In the range E3:E9, the INDEX function returns the fifth value (second parameter) (first argument).Index and Match in excel:To look up the salary for ID 53, swap out the value 5 in the INDEX function (see previous example) for the MATCH function (see first example).

The MATCH function returns position 5 as an explanation. Position 5 is needed for the INDEX function. It makes the ideal pair. The VLOOKUP function is also available if you'd like to use it. You have the choice. To execute complex lookups, you'll need INDEX and MATCH, as we'll see next.Two-way lookup in excel:In a two-dimensional range, the INDEX function can also return a specific value. Use Excel's INDEX and MATCH functions, for instance, to do a two-way lookup.

Note: This above written article is an attempt to show you how to use index function in excel online, 2016 and 2019. This above stated method can be applied on both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go with your work. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.                                  

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