Solved

# Excel index match formula not working

Posted on 2014-09-17
176 Views
I am trying to use Excel's index match functions to pull data from one spreadsheet into another. For now, I am trying to get it to work with the data in a separate tab, but eventually I would like to pull the data in from a separate file.

The spreadsheet with input data looks like this:

The spreadsheet with the output/formulas looks like this:

This is the formula that I am using, but it is returning a #VALUE! error.

``````=INDEX(extract!\$D\$2:\$D\$2000,MATCH(\$B1&\$A2,extract!\$A\$2:\$A\$2000&extract!\$B\$2:\$B\$2000&extract!\$C\$2:\$C\$2000,0),1)
``````
0
Question by:mariita
• 2

LVL 24

Accepted Solution

Phillip Burton earned 500 total points
I wouldn't use INDEX - I would use SUMIFS instead.

The code for A2 would be

=SUMIFS(input!D:D,input!C:C,\$A2,input!B:B,B\$1)
0

Author Comment

A2 is part of the header. The above formula belongs in cell B2. Would SUMIFS still apply?
0

LVL 24

Expert Comment

I meant B2 (oops).
0

## Featured Post

### Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …