Lookup table oddity

  • Thread starter Thread starter Susan
  • Start date Start date
S

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
 
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
 
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.
 
Hi Dave

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

Cheers
Szan

~~~~~~~~~~~~~
 
Back
Top