In reply to KL, Max, Paul & Dave.

1. The source data (table-array) is a defined (as an absolute) name in a
separate excel file, and works fine for most of the lookup values except
certain ones in certain positions in the list.
2. I have included the 'FALSE' value. I tested with the true value, but
understandably if picked up on the next similar reference.
3. The error message is #N/A.
4. I have checked the cell formatting by using other lookup references and
it works fine. The reference I want to look up works fine if I shift it
higher into the table_array.
5.The actual formula "=IF(A80="","",VLOOKUP(A80,Codes,2,FALSE))" where A80
is a numeric and 'Codes' is defined in another spreadsheet as
"=Codes!$A$3:$B$128" where Codes! is the sheet name.
6. A80 in this case is "999" and occurs in cell A120 in the table_array and
should be returning the text in cell B120.

Hope this improves your understanding

regards - pkeegs


"Dave Peterson" wrote:

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