• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index and Match Function

MB028

New Member
Hi, I am not able to use the correct Index Match formula in the attached file.

Question: For a Business Unit, there can be multiple Cost Center Levels. Now, I've a list of these Cost Centers combined and need to find out to which Business Unit they belong to?

Thanks for the help!
 

Attachments

  • INDEX MATCH.xlsx
    10.9 KB · Views: 6
The basic formula in C10 is =INDEX($A$1:$A$5,SUMPRODUCT(--($B$2:$D$5=B10)*ROW($2:$5)))
 

Attachments

  • Copy of INDEX MATCH.xlsx
    10.9 KB · Views: 6
Hi,

Thank you very much for helping me to figure out this formula.

When I'm using the same formula to get my output from my data in two different tabs, it doesn't works. Can you please let me know where am I going wrong in the attached excel/Tab PTBS00-ACCT_CC/Cell:D2
 

Attachments

  • Formula Not working_INDEX.xlsx
    651.1 KB · Views: 8
You should pay attention to
the syntax of sumproduct ( each range has to have the same number of rows)
the range of the INDEX function as the ROW will return a row number starting from row 1
Receiving a solution is one thing, trying to understand it is the most important for the future
 

Attachments

  • Copy of Formula Not working_INDEX.xlsx
    663.8 KB · Views: 3
You should pay attention to
the syntax of sumproduct ( each range has to have the same number of rows)
the range of the INDEX function as the ROW will return a row number starting from row 1
Receiving a solution is one thing, trying to understand it is the most important for the future
Thanks much for help me understanding this formula.
 
Back
Top