• 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:
• 4
• 3
• 2
• +1
2 Solutions

ConsultantCommented:
Use this formula:

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

Kevin
0

ConsultantCommented:
Sorry, I gave you a formula that give you a total.

Kevin
0

ConsultantCommented:
What, exactly, are you in need of?

Kevin
0

Author 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

ConsultantCommented:
Try this formula:

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

Kevin
0

Commented:
are you looking for a vlookup?

try this:

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

Commented:
this index formula works as well:

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

Commented:
sorry, this is the vlookup you would want to use: =VLOOKUP(A4,Projects!\$A\$10:\$C\$29,3,FALSE)
0

Author 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

IT 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.

## Featured Post

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