When you dive into the world of Excel, you quickly realize the power of its functions. Among these, the INDEX & MATCH formulas stand out as incredibly versatile tools for data lookup and retrieval.

Since the XMATCH function is a modern upgrade from the MATCH function, it's only available to those using Excel for Microsoft 365, Excel for the web, the Excel mobile and tablet apps, and one-off ...

In Excel, the INDEX function returns an item from a specific position (in a list, table, database). The MATCH function returns the position of a value (in a list, table, database).

This guide explores key Excel functions—FILTER, XLOOKUP, SUMIFS, INDEX-MATCH, and VLOOKUP—detailing their unique strengths, practical applications, and potential limitations.

Now, we can use INDEX and MATCH to look up how much traffic the contact page (or any page) received in the month of January (or any month) just by typing in G2 and G3.

From here, let’s say we want to add all of our existing broad match keywords as exact match so we can refine our bidding control on them. In Excel, add a Filter to your report (ALT-A-T).

if_not_found: Text returned when a valid match isn’t found. If omitted, the function returns #N/A. match_mode: Specifies the match type. See Table A for the appropriate values and explanations.

MATCH searched for "Math" in C2:C9, then passed the result (2) to INDEX, which returned the second element of the array B2:B9, which was New York. So the math major is from New York.

Set the fifth parameter [match_mode] to 1 and if there isn’t an exact match you will get the next largest result; -1 fetches the next smallest item.