Lookup and Determine Results Based on One to Many Relationship

**chunghyu** · 08-22-2012, 11:24 AM

Hello Excel Gurus,

I would like to request your expertise with Excel formulas & functions. I have 2 worksheets (see below). In Sheet1, one project may have multiple funding sources and budget amounts. In Sheet2, I am trying to determine for each project the funding source with the highest budget amount. In Sheet2, Column C (Funding Source) illustrates the desired results.

I do not have experience with developing custom code (i.e. VBA) in Excel and would like to utilize the Excel formulas & functions to determine the desired results, if feasible.

Thank-you in advance for your timely response.

Sheet1
Sheet1.JPG

Sheet2
Sheet2.JPG

**tigeravatar** · 08-22-2012, 11:47 AM

chunghyu,

Welcome to the forum!
I'm not sure what your tables are called, but assuming the table in Sheet1 is Table1 and the table in Sheet2 is Table2, then in Sheet2, C2 and copied down:

Please Login or Register  to view this content.

**chunghyu** · 08-22-2012, 03:50 PM

tigeravatar,

Thank-you very much. I appreciated your timely response. Your example formula worked and saved me endless hours trying to figure it out.

Thanks again!

chunghyu

**tigeravatar** · 08-22-2012, 03:53 PM

You're very welcome

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save