You can also simply set your original vlookup using the COLUMN() function for the column with the value to return. A simple version has your table's lookup value column in Column A and the value to return is in any other column, Column L say. Then use:
COLUMN(L:L)
for the value to return's column. More complicated, as in the lookup column is not Column A? Just subtract from the above: (for lookup in Column C)
COLUMN(L:L)-2
The value here is that adding and deleting columns does not break your formula as Excel automatically fixes it. Add a column? It becomes COLUMN(M:M). And so for deleting a column. All handled by Excel. And it is truly easy to do. Columns like "WYO"? Excel doesn't care. Find the column number of your lookup column with a quick COLUMN() formula in that column, then you have the value to subtract. Easy.
No doubt less powerful than Index/Match and practice makes perfect there so doing this holds you back. But it is a quick result that lasts. And you can teach it quickly to others while you hone your Index/Match skills. It is also not as intuitive as using Named Ranges, but many times you cannot (Tables anyone?). (And it can be the underlying value for the Named Range, for that matter.)
So, if you want a quick, simple, hard to break with simple column changes (it will keep its reference if cut and reinserted else-column, rather than relatively look from its new position, but for this kind of work, I think that's a definite plus), then this is a solution to think on.
While I was typing out my answer, you had already sent yours. My goal was to show the OP how to use the
match
function to suit his specific needs. Please feel free to down-vote should my answer be erroneous. – Ellesa – 2011-08-11T19:58:36.800The suggestion to use Index-Match is the best solution to this issue. I think you should add an example to your answer and make the world a better place. – Doug Glancy – 2011-08-12T15:22:05.873