• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

I need an excel formula using index match

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
wrt1mea
Asked:
wrt1mea
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
Use this formula:

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

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Sorry, I gave you a formula that give you a total.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
What, exactly, are you in need of?

Kevin
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
wrt1meaAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
Try this formula:

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

Kevin
0
 
KnutsonBMCommented:
are you looking for a vlookup?

try this:

=VLOOKUP(A4,Projects!A10:C29,3,FALSE) and drag down
0
 
KnutsonBMCommented:
this index formula works as well:

=INDEX(Projects!$C$10:$C$29,MATCH(Summary!A$4,Projects!$A$10:$A$29,0),0)
0
 
KnutsonBMCommented:
sorry, this is the vlookup you would want to use: =VLOOKUP(A4,Projects!$A$10:$C$29,3,FALSE)
0
 
wrt1meaAuthor Commented:
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
 
Russell LucasIT Infrastructure Project ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now