Share this article
Improve this guide
How to Use Excel INDEX and MATCH Functions in Formulas
The index and match functions can be combined in a formula
3 min. read
Updated onOctober 7, 2024
updated onOctober 7, 2024
Share this article
Improve this guide
Read our disclosure page to find out how can you help Windows Report sustain the editorial teamRead more
The INDEX function in Excel is used to get the value of a cell from a table when you specify the row and column numbers. In contrast the MATCH function provides the position of a cell in an array that has a certain value. Our article will guide you on how to use the INDEX and MATCH in formulas on Excel.
How do I use INDEX and MATCH formulas in Excel?
1. Understanding the functions
2. Basic example
Let’s say you have a table of sales data:
You want to find the sales for Product B.
3. Using MATCH to find the row number
This formula returns2becauseBis in the second row of the range A2:A4.
4. Using INDEX to find the sales value
This formula returns 200, which is the sales value for Product B.
5. Combining INDEX and MATCH functions
We are addressing the INDEX and MATCH formulas because you can combine them conveniently.
This combined formula will return 200, the sales value for Product B, in one step.
If you have a more complex table and want to look up values based on multiple criteria, you can use a combination of INDEX and MATCH with nested functions.
To find the sales for Product A in the South region:
This formula returns 150, the sales value for Product A in the South region.
Additional tips for using INDEX and MATCH
By following these steps, you can effectively use INDEX and MATCH functions in formulas and even combine them to perform powerful lookups in Excel.
If you want to learn more functions and formulas in Excel, we also have guides on how tocount distinct values in a tableand how toadd bold text in formulas.
Our latest guide shows you how to use theExcel XLOOKUP formula with multiple criteria, so don’t miss it for more information.
For any questions or suggestions, feel free to use the comments section below.
More about the topics:Microsoft Excel
Claudiu Andone
Windows Toubleshooting Expert
Oldtimer in the tech and science press, Claudiu is focused on whatever comes new from Microsoft.
His abrupt interest in computers started when he saw the first Home Computer as a kid. However, his passion for Windows and everything related became obvious when he became a sys admin in a computer science high school.
With 14 years of experience in writing about everything there is to know about science and technology, Claudiu also likes rock music, chilling in the garden, and Star Wars. May the force be with you, always!
User forum
0 messages
Sort by:LatestOldestMost Votes
Comment*
Name*
Email*
Commenting as.Not you?
Save information for future comments
Comment
Δ
Claudiu Andone
Windows Toubleshooting Expert
Oldtimer in the tech and science press, with 14 years of experience in writing on everything there is to know about science, technology, and Microsoft
