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

On Sat, 01 Jan 2005 23:43:30 +0100, Aladin Akyurek wrote:

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 wrote:
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.

--
Mike H

On Sat, 01 Jan 2005 22:30:06 +0100, Aladin Akyurek wrote:


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

=VLOOKUP(LookupValue,Table,ColIdx,0)


Mike H wrote:

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?