You have hard-coded the 1, 3 and 5 column index... and you don't really have a choice, given the shape of your data.
The problem is that your table is not a table: it's essentially a pivot. This would be a table:
If you can un-pivot your top table into an actual table like above, then you could have a much simpler, easier to read (and to maintain!) table formula - given a
Table1 table name for the above table:
Excel likes data that's organized in rows and columns; if you have 2 levels of headings, and repeating columns under each heading, you haven't organized your data in rows and columns, and you've set yourself up for complicated lookups - as you've noticed.
By laying out your data in a table, you make a database-friendly list of records that can easily be sourced from a CSV or XML file, or directly from a database table or view.
Having 3 times the same [Id] and [Name] columns under a different heading, looks like you have 3 identical tables, with different values: by combining them into one (and adding a column to identify which of the 3 tables it's coming from), lookups become much, much simpler.
Other than that, I would add the following:
- The bottom table should be turned into an actual table, so leverage table formulas.
D19 would then say
[@Id], which is much easier to understand.
- The top table should be on its own worksheet, and adding rows in that table should not force you to update the formulas in the bottom one - as it stands, you'll have to be careful about that.
- If you can't turn the top table into an actual table, consider using named ranges to abstract
$C$12:$C$14 & friends behind a name. You'll still have to be careful when adding rows, that the new rows are included in the named range - but if the formulas in the bottom table refer to named ranges instead of addresses, you won't need to modify the formulas, just to verify the reference the named ranges point to.
5 are the only valid values, you could name them:
That way you can replace the hard-coded 1, 3 and 5 by "Funding", "Lending" and "Example" in your formula, giving a meaning for these numbers:
But I would really push to remove the first-level heading and turn it into a column: logically, it's a piece of data - it belongs at the row level, not as a heading. Your worksheet will be much easier to extend that way.