10
\$\begingroup\$

I'm trying to get table header name in excel. I tried it using index, IF and MATCH. It works but I am not sure whether my formula is the best or not.

This is how my sheet looks:

enter image description here

And here is my formula to get produk name (E19:E22):

=INDEX(
   $C$10:$H$10,0,
      IF(IFERROR(MATCH(D19,$C$12:$C$14,0),0),1,
      IF(IFERROR(MATCH(D19,$E$12:$E$14,0),0),3,
      IF(IFERROR(MATCH(D19,$G$12:$G$14,0),0),5,0
))))
\$\endgroup\$
10
\$\begingroup\$

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:

a table with "Id", "Name" and "Category" columns

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:

=INDEX(Table1[Category],MATCH([@Id],Table1[Id],0))

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.
  • If 1, 3 and 5 are the only valid values, you could name them:

value "1" is named "Funding"

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:

=INDEX(
   Categories,0,
      IF(IFERROR(MATCH(D19,FundingIds,0),0),Funding,
      IF(IFERROR(MATCH(D19,LendingIds,0),0),Lending,
      IF(IFERROR(MATCH(D19,ExampleIds,0),0),Example,0
))))

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.

\$\endgroup\$
  • \$\begingroup\$ hmm okay.. I am learning excel so it's no prob to change it. And I think you are right it's a piece of data and should be treat as table like in database. at least I know in this case (get header name) my formula is the good one. Thanks!! ;) \$\endgroup\$ – Kakashi Jan 25 '15 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.