How to Apply INDEX and MATCH Separately and Combined | Advanced Excel
Originally published: 26/11/2018 13:57
Publication number: ELQ-36098-1
View all versions & Certificate
certified

How to Apply INDEX and MATCH Separately and Combined | Advanced Excel

Learn how to apply both functions, INDEX and MATCH, separately and combined on Excel.

  • Step n°1 |

    INDEX

    INDEX is a formula which returns the value located at a given intersection within an array.


    So basically, the INDEX formula needs us to indicate the following:
    1) An array where it will function
    2) A row number
    3) A column number


    The formula will go and find the specified row and the specified column within the array and deliver its content.


    For example if we select the array from B4 to C12 in our table, and choose 5 as the row argument and 2 as the column argument, we will obtain “Italy” as a result. The INDEX function simply delivers the cell which has the coordinates we chose. Within the fifth row and second column of the range we selected lies “Italy.” Right? We indicated those coordinates and INDEX provided us the result. Let’s do another try. I’ll select the same range – from B4 to C12. This time, let’s pick 1 as the row argument and again 1 as the column argument. The result is “Borussia,” given that the value in the first row and first column of the selected range is “Borussia.”

    How to Apply INDEX and MATCH Separately and Combined | Advanced Excel image
  • Step n°2 |

    MATCH

    The next function we will see in this video is MATCH. It returns the relative position of an item within an array.


    Let’s illustrate that with an example. I’ll type the MATCH function. The first argument that needs to be selected is the lookup value – in our example, that will be “Milan”, lying in B19. After that, we need to specify which is the array where the lookup value’s position needs to be found. Let’s select all teams that lie within the range from B4 to B12. The third argument is a logical value – “0” or “1,” standing for “an exact match” and “closest match.” I’ll select 0 as we need an exact match. Our formula is ready.


    The output of the function is “2,” which represents Milan’s position within the selected array.


    It is important to notice that the formula can be applied also vertically. For example, if we go on and look at number of games played within the array from B3 to F3, the result will be four, which is correct as “Games Played” is the fourth column within the array.

    How to Apply INDEX and MATCH Separately and Combined | Advanced Excel image
add_shopping_cartContinue reading for free (70% left)

Reviews

  • Be the first to review this How-To Method

    Write a review


keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from 365 Data Science

See all
keyboard_arrow_leftkeyboard_arrow_right

Discussion feed for How To Apply INDEX And MATCH Separately And Combined | Advanced Excel

The user community and author are here to help. Go ahead!


0.0 / 5 (0 votes)

please wait...