Just to add to Max's response:

And if there's nothing else below those rows:
=VLOOKUP(C1,F:G,2,0)

I like to put my lookup tables on separate sheets.

Then I know that there's nothing under them:
=vlookup(c1,sheet2!a:b,2,false)

And I never have to worry about adjusting that range.



Max wrote:

"pkeegs" wrote:
... VLOOKUP won't pick up some of the end items.
If I move the reference up the list it will work.
However if I leave it at the end and
remove a large middle section of the list, it will
still not work....

One guess hazarded purely from the above lines ...

Think you might have forgotten to fix* the table_array part in the VLOOKUP
formula in the starting cell before you copied down, that's why the weird
results ..

*i.e. make the table's cell references absolute

Instead of say, in the starting cell:
= VLOOKUP(C1,F1:G700,2,0)

Put it as: = VLOOKUP(C1,$F$1:$G$700,2,0)
(with the table_array fixed with "$" signs: $F$1:$G$700)

Then only copy down ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

--

Dave Peterson