Wednesday, 12 December 2007

Cool Excel tip to make sure VLOOKUP continues to work if columns move

I'm a fairly advanced user of Excel so generally I find hints and tips fall into two categories: either I already know them, or they relate to features I never use (like functions way beyond my meagre grasp of maths or finance). But today I read a really cool tip that 1) I'd definitely use and 2) it never occurred to me to try before.

This is from David Gainer's blog, I hope he doesn't mind me reproducing it here...

Here’s a trick to using VLOOKUP with tables. VLOOKUP isn’t table-aware, so the third argument, col_index_num, is still a number and not, say, a column name when used with Tables. Next time you use VLOOKUP on a table, use the following formula for the third argument instead of an index number:

MATCH( TableName[[#Headers], [ColumnName]], TableName[#Headers], 0)

Substitute ‘TableName’ with the name of the table you are looking up, and ‘ColumnName’ for the name of the table column you want to index into. This, in effect, gives you a tight pointer to the column that does not break if the column name changes or if the column is re-arranged in the table, thereby providing a more robust alternative to using a numerical index.

1 comment:

  1. Love it. 5 years since you posted this, without a comment, but very helpful thanks :-)


Creative Commons License This work by TechieBird is licensed under a Creative Commons Attribution-No Derivative Works 2.0 UK: England & Wales License.