Vlookup and sorting

  • Thread starter Art at ABE Computer Consultants
  • Start date
A

Art at ABE Computer Consultants

I created a number of vlookup fields that lookup values in a sheet
that is to be populated by the user. I found that the sheet that has
the values must be sorted to return the values correctly. I have come
upon a problem, though. I can sort the "data" sheet and everything is
fine. But when I go the the lookup field, not all of the data is
shown. Here's an example:
In the data field there are values for lumber sizes (2 x 4 x 8, 2 x 4
x 10,2 x 4 x 12). I sort them and they are sorted as 2 x 4 x 10, 2 x
4 x 12, 2 x 4 x 8. This is no big deal--at least they are sorted.
BUT--when I go to the lookup field, I see only 2 x 4 x 12 and 2 x 4 x
8. Why? Why has it decided not to show all the values? This causes
the user a bit of heartburn.

FYI, the spreadsheet is written for Office 2000, but performs exactly
the same in Office 2003.

Art
 
J

JulieD

Hi Art

if you'ld like to zip it & email it directly to me i'ld be happy to have a
look at it.

Cheers
JulieD
julied_ng at hcts dot net dot au
 
E

Earl Kiosterud

Art,

Try using FALSE for the 4th argument.
=VLOOKUP(A2, Table, 2, FALSE)

This finds exact matches only, which is what you want anyway, and the list
need not be in any order.
 
A

Art at ABE Computer Consultants

Earl Kiosterud said:
Art,

Try using FALSE for the 4th argument.
=VLOOKUP(A2, Table, 2, FALSE)

This finds exact matches only, which is what you want anyway, and the list
need not be in any order.
Click to expand...

Ahhhhhhhhhh--just had an IQ surge. I was in the process of seeing if
this peculiar error occurred everywhere in the spreadsheet and I
noticed that it was happening in just one part. I finally figured it
out--I had the name for the validation list incorrectly defined. I
had missed the very top row in the list. Sheesh!

Anyway, thanks to you all. You got me thinking and it's amazing what
one can do if they actually use their brain!

Art
 
Top