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.
Love it. 5 years since you posted this, without a comment, but very helpful thanks :-)
ReplyDelete