Create own formula to replace VLOOKUP or INDEX & MATCH

Hi guys,

I'm currently working in the Finance Controlling department for a large Fortune 500 company.
In this role, I often create budget overviews for my business partners.
Our financial system only provides me with an overview per cost center (a part of an organization to which costs may be charged for accounting purposes), but not per Budget name and/or budget owner.
This is why I rely on one single underlying mapping table to link a cost center to a budget name and budget owner.

I currently use INDEX & MATCH to link the two together.
I do this multiple times per day and although it's a relatively efficient formula, I'm looking for a way to create my own formula to replace INDEX/MATCH to help me save time.

In practice, I want to create a formula called BudgetOwner, that automatically returns "Frank Underwood", when I refer to cost center "USA001" (see mapping table below)
So in essence, BudgetOwner(A1) = "Frank Underwood"

Can anyone help me out with the coding?
The mapping sheet is stored on a central location, so ideally I would have Excel consult the mapping table in that location, so that any changes/updates to the mapping are picked up automatically.

A greatly simplified version of the mapping table is shown below.

Cost Center Budget name Budget owner
USA001 Sales Frank Underwood
USA002 Sales Frank Underwood
USA004 Marketing Tom Johnson
USA005 Sales Frank Underwood
USA006 Marketing Tom Johnson
USA007 Marketing Tom Johnson

I've been exploring the functionalities of PowerPivot as well, but it's not exactly what I'm looking for.

Thanks a lot for your help on this!

Br,

Kris

Are you looking for a VBA solution, then?

Unless the order of the mapping table changes, I don't see the point of a two way match/index. The VLOOKUP is a more convenient approach. And a dynamic named range makes it simpler.

If you put it in VBA, you'll need to maintain code and the workbook will need to be macro enabled.