Hopefully a simple problem

[chaostheory]

We are having an issue that we have never come across. We sorted a very large spreadsheet of data pasted from access. And we are trying to find values using VLOOKUP.. Here is the problem, for example.

01245 5
01246 5
01247 5
01245a 6
01245ab 6
01246a 6
01246ab 6
01247a 6

This is how data is sorted when you sort with excel. It puts all letters at the bottom. When we use Vlookup, if we are looking for 01245ab, the vlookup is stopping at 01245 and returning value of 5 because 01246 comes after it. It knows its sorted but doesnt realize letters show up at the bottom. Is there a simple way to make vlookup use the whole list to look for an exact match and not stop at 01245

 

[rainman89]

havent used a vlookup before, but couldn't you put a wild card after it and use a LIKE to grab anything that starts with 01245?

Like 01245*?

Just a suggestion. Might not be the right one

 

[chergh]

When I sort the data with excel the order I get is:

01245
01245a
01245ab
01246
01246a
01246ab
01247
01247a

Looks like you are sorting on colum2 first and then column 1

 

[chaostheory]

When i copy and paste your exact numbers into excel, give it a header of Skid, and sort by that row it gives me this. What version of excel are you using, we are on 2003. Also im sorting by going to Data, Sort AZ

Skid
01245
01246
01247
01245a
01245ab
01246a
01246ab
01247a


We were able to solve the problem for our needs by creating a new column to the left of the previous one, and using =left(B1,5) to get the first 5 digits (minus the letters) and then sorting by that. So it made all the 01245 next to each other since there were no letters, then the vlookup worked great. What a strange issue though...

Ours now looks like this. We sorted by A, then column B looked ok.

01245 01245
01245 01245a
01245 01245ab
01246 01246
01246 01246a
01246 01246ab
etc

 

[Brianwarnock]

Wouldn't a range lookup of False have given you what you wanted?

Brian

 

[lemo]

what exact formula are you using?
i agree that lookup with 'false' should do it.
something like -
=VLOOKUP(VALUE,RANGE,COLUMN#,FALSE)

l