Agreed on the use of MATCH to find the correct column, which will work
fine until someone renames the column header, unless you use the column
header cell as the criterion in the MATCH function. Regardless, now we
really have an ungainly formula to achieve a simple look-up. However,
it would work ... until someone moves the Product Name column to the
left of the the Product ID column, when it would fail ... but the
relatively simple INDEX/MATCH combination suggested in a previous post,
like the Energizer bunny, would keep on running.

Named ranges alone won't protect against inserts unless you still use
some method to make the column index number relative or variable rather
than absolute.

VLOOKUP with an absolute column index number is a nice simple formula
that is ok if you can be absolutely sure that no-one will insert
columns or move the sought column to the left of the criterion column
for the life of the spreadsheet, but it's not a risk I like to take.
Even if you have control of the spreadsheet for ever more, it limits
your flexibility in future maintenance and you have to remember not to
do certain things. I just don't believe it is a good practice.

Declan O'R