I have a list in Excel that is reasonably long, 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. All formulae have been copied, I have reformatted all the
cells the same, names (database) in the formula have a big enough reference.
Any suggestions?

Hi pkeegs,

It could be useful to see an example of the formula that doesn't work, the
data used, the error message or the incorrect value and the desired value.

Regards,
KL


"pkeegs" wrote in message
...
I have a list in Excel that is reasonably long, 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. All formulae have been copied, I have reformatted all the
cells the same, names (database) in the formula have a big enough
reference.
Any suggestions?

"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
----

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)
if the fourth argument(range _lookup,true or false) is omitted it defaults
to true which means the data must be sorted in ascending order,if false it
will find only an exact match.and the data doesnt need to be sorted
--
paul
remove nospam for email addy!



"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
----

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

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

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

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

... It's a bit hard without the files in front of you.

Perhaps you could try uploading your file(s) and then post a *link* here for
those interested to check it out. There are 2 free filehost that I know of,
and use:

http://www.savefile.com/filehost/
http://flypicture.com/

Believe that we're earnest in trying to help you resolve the problem ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

This:
=a80=codes!a120
is equivalent to:
=if(a80=codes!a120,true,false)

So it's showing that the value in A80 is not the same as the value in A120.

Did you try the paste special|Values stuff?

pkeegs wrote:

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


--

Dave Peterson