Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel index match formula not working

Posted on 2014-09-17
3
Medium Priority
?
209 Views
Last Modified: 2014-09-17
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:
input
The spreadsheet with the output/formulas looks like this:
output
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)

Open in new window

0
Comment
Question by:mariita
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40328107
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

by:mariita
ID: 40328123
A2 is part of the header. The above formula belongs in cell B2. Would SUMIFS still apply?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40328174
I meant B2 (oops).
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question