By far the most common data wranging question that I get asked by friends and colleagues is “Without having to use R, how do I pull values from one table into another, based on matching shared values in another column?”

The answer is the almighty INDEX MATCH function in Excel. Actually, although I call it “the INDEX MATCH function,” it’s a MATCH function nested inside an INDEX function (basically, the MATCH function tells the INDEX function which value to pull). And it works like this…

Basic structure of the INDEX MATCH function

Basic structure of the INDEX MATCH function

An Example

In the following example, I have two tables: The data that I’m working with is a table with information about orangutan focal follows (each row is one focal follow with a unique follow number, a date, and the orangutan’s name). The table from which I want to pull values has some information about each individual orangutan (in this case, it’s the age-sex class). I want to add a column to the data that I’m working with (the follow data), which has the age-sex class of the focal orangutan. Of course, in this example, there are just a few follows and 5 different individuals. But, imagine if there were hundreds of follows over several years, and tens of different individuals. Definitely a job for the INDEX MATCH function! Here we go….


The problem

The problem


The solution

The solution


Et voilà!

Et voilà!


Pull down to fill the whole column

Pull down to fill the whole column


Et voilà again!

Et voilà again!


Further resources

This ExcelJet post has a lot more detail and explanation about what this INDEX MATCH function is actually doing and how it works.

Note that if you want to match based on multiple criteria (i.e. values in two or more separate columns), there are basically two options using this general approach.

  1. You can use an array formula - head over to this ExcelJet post to find out how.

  2. I often use a quick and hackey way of doing it if I want to match based on values in two columns: in both the data I’m working with and in the table that I want to pull values from, I make a new column that is a composite of the two columns that I want to match by (= MatchCol1 & MatchCol2), and then I just use a regular INDEX MATCH function using that new column. Just remember to “Paste Special” >> “Values” to the new data you’ve pulled before you delete the ugly composite columns.