Auto-populating a table based on ranked values

Hey all,

I am looking to create a table with ranked projects from a different tab that is able to pull data from the same tab and change based on value updates. It is difficult to explain, so I have attached the file below.

What I am looking to do is pull the ranked projects from tab #7 - Scoring to tab #5 - Final list. Each project has a unique ID#, but a lot of the columns I need populated are not unique. I tried using a combination of arrays, but it always returned a #value into the cells. If someone could help with this it would be much appreciated.

P.s: In tab #5 - Data sheet, the formulas in I5 and P5 work, but are pretty ridiculous, and I'm pretty sure there must be an easier way. I was looking up values from the 8-budget tab to try and determine which group column H values were from. The only complication I was having was that a project can be allocated to either a functional unit, or group so I need to have the "group" total as independent, not just as a sum of the functional unit totals. Like I said, these formulas work, I was just wondering if somebody had a better solution than a massive imbedded if function


Thanks a ton!

BWellman

Excel_Test#3.xlsx

I would ideally leave "7-scoring" as is to eliminate as much work as possible for the person using the tool (just want them to input data on tab #4- data sheet, then look to tab #5 for ranking). I would say top 30-ish probably. I was thinking some form of index match, but it hasn't been working so far.

Hey,

Thanks a ton. The formula you posted needed a little bit of tweaking, but it gave me the insight I needed to get it done!