"-" ignored in sort

[Mike H]

The hypen character "-" seems to be ignored in a sort that I'm using.

Here are the details:
I have a worksheet that uses vendor name and partnumber in the form of
"vendor-part" that then uses vlookup to query a vendor file with these
fields applicable fields:

column a column b column c
=B1&"-"&C1 vendor part

Strangely, to me, a sort using column A doesn't produce the same results
as a sort using column B plus column C.

For example, using only column A for the sort:
A-C75A A C75A
ACB-15 ACB 15
A-CG1850 A CG1850

using column A + column B
A-C75A A C75A
A-CG1850 A CG1850
ACB-15 ACB 15

As a result, if I use TRUE in the range_lookup portion of the vlookup
function I have no idea what the outcome will be. I don't know if
vlookup is considering a sorted array in the same way that SORT is.

I suppose this is a two-part question:
1) what would the result be of a lookup for A-C8? I seem to get A-C75A
using a column A sort and #NA using a column B+C sort.
2) given the sorting behavior, how do I want to sort this table, or
which character besides a hypen can use to get both good vlookup results
and sensible visual results as well?

 

[Aladin Akyurek]

I guess you need to set the match-type to 0 (or FALSE)...

=VLOOKUP(LookupValue,Table,ColIdx,0)

 

[Mike H]

Hi Aladin. Yes, that works, and it's what I've had to resort to in the
interim. Unfortunately, the next lower part number would be a valid
response, so I'm stumbling along with a partially broken spreadsheet.

 

[Aladin Akyurek]

You seem to have an area in A:C as lookup table. It seems that column A
is a concatenation of column B and C. You should be to do lookup with
match-type set to 0 if A:C is not sorted on column A...

=VLOOKUP(X2,A:C,3,0)

where X2 houses a lookup value. The value in X2 must have the same
structure as the values in column A.

If you sort A:C on A in ascending order, you should be able to do a
faster lookup...

=IF(LOOKUP(X2,A:A)=X2,LOOKUP(X2,A:A,C:C),"")

 

[Mike H]

Aladin, you're right in your first statements below; that's exactly what
I'm doing now (except I can't use it as is for next-lower value
lookups). Still, it is a large spreadsheet with 5 other linked
spreadsheets and the vlookup is slow.

I'm going to try out your suggestion for vector lookups (using the "if
found" test) and see what happens. It'll be a while, but I'll post back
any observations. It should be interesting. Thanks for the tip.

Mike

 

[Dave Peterson]

From xl2002's help (for: Troubleshoot sorting)

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
strings are the same except for a hyphen, the text with the hyphen is sorted
last.

Can you use a period instead?

or even
=b1&char(10)&c1
This will be like an alt-enter if the cell is set for wrap text--else it'll show
a little square.

 

[Myrna Larson]

The hypen character "-" seems to be ignored in a sort that I'm using.

Yes, that's what you'll find in Help.

 

[Mike H]

That's it! Used an underscore instead and we're off and running. Thanks,
Dave. btw, I can think of uses for the char(10) tip as well

 

[Mike H]

Yes, that's what you'll find in Help.

and so it is, when after reading your post I thought to use the key
words "sort order". I've been sorting for so many years without the
issue coming up that it just didn't occur to me to query help on such a
basic level. Perhaps it hasn't always been that way. In any event, yes,
you're right.