?
Solved

I need an excel formula using index match

Posted on 2011-02-28
10
Medium Priority
?
261 Views
Last Modified: 2012-06-22
I am trying to use an index match formula that references a range of cells on another tab, but cant seem to get the syntax right. See the attached example.

I want to index Column C on the Projects tab with a Code on the Summary Tab to return the amount in column D (shaded yellow) for the respective Code.

I am trying to make an example without actual data. I am trurning values, but they are for the incorrect amounts so I figured it might be better if I just start over.

Here is the prodcution formula i was using that returns incorrect totals :=INDEX(Projects!$U$10:$U$275,MATCH($B4,$B$500:$B$1500,0))


EX-Exch-Test.xlsx
0
Comment
Question by:wrt1mea
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34999440
Use this formula:

   =SUMIFS(Projects!C10:C29,Projects!A$10:A$29,A4,Projects!B$10:B$29,B4)

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34999487
Sorry, I gave you a formula that give you a total.

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34999499
What, exactly, are you in need of?

Kevin
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:wrt1mea
ID: 34999572
OK, I know I did a poor job of explaing so here is another attempt. On the Summary tab, the name has a drop down. I have the index match set for colum b and column C. I wanted to have the same for column D. Colum D would ference the Projects tab, C10:C29 for the cost figure that relates to that specific code.

For Example:  On the summary tab, If I select project 130, it returns Code 91008, Status "Closed" and the Amount (from the Projects tab) should be $1,008.
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 800 total points
ID: 34999586
Try this formula:

=SUMIFS(Projects!C$10:C$29,Projects!A$10:A$29,A4,Projects!B$10:B$29,B4)

Kevin
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 34999685
are you looking for a vlookup?

try this:

=VLOOKUP(A4,Projects!A10:C29,3,FALSE) and drag down
0
 
LVL 6

Accepted Solution

by:
KnutsonBM earned 1200 total points
ID: 34999707
this index formula works as well:

=INDEX(Projects!$C$10:$C$29,MATCH(Summary!A$4,Projects!$A$10:$A$29,0),0)
0
 
LVL 6

Expert Comment

by:KnutsonBM
ID: 34999722
sorry, this is the vlookup you would want to use: =VLOOKUP(A4,Projects!$A$10:$C$29,3,FALSE)
0
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 34999782
Accepting multiple solutions as a couple of worked, just the index worked a lilttle better because of grammar differences in the projects names on the production sheet.

Thanks for all of the support!
0
 
LVL 6

Expert Comment

by:Russell Lucas
ID: 34999783
Your MATCH array is different to your INDEX array so you will  return a mismatched results.

I believe what you want to do is this:-

=INDEX(Projects!$C$10:$C$275,MATCH(B4,Projects!$B$10:$B$275,0))

It goes in D4 on Summary and copy down.

INDEX creates a list of values that you want to return (in this case the Values from Projects in column C) then returns a value from the row you specify which is where MATCH comes in.

MATCH gives you the row number that a specific value appears in a INDEX. So you give it the value you are looking for (B4) then the search index (the Codes from Projects in column B)
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

752 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