Hi Dave
not sure what the =a80=codes!a120 should have achieved but it returned a
FALSE.
Have tried all the suggestions for reformatting with no result. I will take
the sheets to a local guru and see what they can come up with. It's a bit
hard without the files in front of you.

Regards & thanks - pkeegs

"Dave Peterson" wrote:

Since you can see the where the match takes place, try this in an empty cell:

=a80=codes!a120

Excel will not see 999 (a number) and '999 (text) the same.

Try formatting the cell as General (or some numeric format)
then hit F2 and enter.

In fact, do that both for a80 and codes!a120.

My bet is you have some text numbers and number numbers in the range (or both
ranges).

One way to fix this (convert them all to number numbers).
select an empty cell
Edit|copy
select that range (whole column???) and
edit|paste special|check Add.

(I'd do both ranges if I were you.)



pkeegs wrote:

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


--

Dave Peterson