{"id":2825,"date":"2020-12-21T09:47:50","date_gmt":"2020-12-21T17:47:50","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=2825"},"modified":"2020-12-21T09:48:39","modified_gmt":"2020-12-21T17:48:39","slug":"how-to-use-index-match-formulas-to-pull-data-from-horizontal-columns","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=2825","title":{"rendered":"How to Use INDEX MATCH Formulas to Pull Data from Horizontal Columns."},"content":{"rendered":"\n

Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:<\/p>\n\n\n\n

  1. VLOOKUP is a much simpler formula. If you’re working with large data sets that would require thousands of lookups, using the INDEX MATCH function will significantly decrease load time in Excel.<\/li>
  2. INDEX MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you’d have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and\/or lead to errors.<\/li><\/ol>\n\n\n\n

    So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren’t the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I’d choose to do an INDEX MATCH instead.<\/p>\n\n\n\n

    Let’s look at an example. Let’s say Sheet 1 contains a list of people’s names and their Hogwarts email addresses, and Sheet 2 contains a list of people’s email addresses and the Patronus that each student has. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a “Patronus” associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. I’d use the INDEX MATCH formula instead of VLOOKUP so I wouldn’t have to switch any columns around.<\/p>\n\n\n\n

    So what’s the formula, then? The INDEX MATCH formula is actually the MATCH formula nested inside the INDEX formula. You’ll see I differentiated the MATCH formula using a different color here.<\/p>\n\n\n\n

    The formula: =INDEX(table array, MATCH formula)<\/strong><\/p>\n\n\n\n

    This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))<\/strong><\/p>\n\n\n\n

    The formula with variables from our example below: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))<\/strong><\/p>\n\n\n\n

    Here are the variables:<\/p>\n\n\n\n