Solved

Excel index match formula not working

Posted on 2014-09-17
3
180 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 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