?
Solved

I need an excel formula using index match

Posted on 2011-02-28
10
Medium Priority
?
267 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
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…

840 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