Lookup table oddity

[Susan]

Hi
I have a lookup table that was in an assessment exercise and it refuses to
work on one cell.

There are two lookup tables in the exercise... one looks up the product
code - this one works okay. The one that won't work properly is the second
one, which uses the supplier as the lookup value.

I tested it out on a computer with 2003 at the training centre where I work
and it worked.

However, it won't work on my 2007, or any of the computers using 2007 that I
tried it on.

I can attach the file for anyone who is interested in looking at this
oddity.

It is driving me distracted and I would love to know why it won't work. I
have always considered 'lookup' tables to work provided I put the correct
information in - this has me stumped and a bit concerned about the accuracy.

I installed service pack 2 to see if it would help but it didn't.

Any thoughts or similar experiences?

Szan, Australia

 

[Dave Peterson]

Does the value you're trying to match consist entirely of digits?

If yes, then I bet in it's a real number in one location and text masquerading
as a number in the other.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

 

[Susan]

Hi Dave
Thank you for your response.
It is odd. (Ooops... not your response, the 'glitch' in the
exercise.<smile>)
Column A, sheet 1 has the catalogue numbers; Column B sheet 1 has the
Supplier names; Column C, sheet 1 has the item description, Column D, sheet
1 has cost price. This is the first lookup and it will work. Column E has
freight costs and it looks up all except on supplier.

The lookup tables were on Sheet 2.
Lookup table 1 has 2 columns, Catalogue number in one, Price in the other.
The lookup value is the Catalogue number and the Column index number is 2
The catalogue numbers are a mix of letters and numbers - but they work.
This lookup returns the correct value on all the cells.

Lookup table 2 also has two columns - the supplier name in one and the
freight costs in another. For some reason it will not return the correct
value on one of the suppliers.

I took it to a colleague and asked him what I was missing because I tried it
so many times and different ways. It stumped him too. He suggested I try
HLookup - but that didn't work either.

The VLookup exercises worked in Excel 2003 but the only way I can make it
display the correct value in that cell is to link the cells. (Not the best
option.)

Thank you for the link - I will wander off there later and see if I can
identify anything similar.

I do appreciate your response.
Szan, Australia

 

[Dave Peterson]

If you know that there is a match, then you can (manually) find the two cells
that should be the same.

Say A32 of the activesheet
and
A372 in Sheet2

So find a few cells empty cells in the activesheet.

Type:
=a32='sheet2'!a372
=isnumber(a32)
=isnumber('sheet2'!a372)

What is returned by all 3 of those formulas?

And you haven't shared the actual formula that you're using, but if you're
looking for an exact match and using =vlookup(), make sure that the 4th argument
is False or 0.

=vlookup(a2,'sheet2'!a:e,3,false)

If this doesn't help, share your formula.

 

[Susan]

Hi Dave

I replied twice to this post but it didn't appear.
Checking if this one will

Cheers
Szan

~~~~~~~~~~~~~

 

[Dave Peterson]

This made it.

Hi Dave

I replied twice to this post but it didn't appear.
Checking if this one will

Cheers
Szan

~~~~~~~~~~~~~