At 10:49 29/05/2009 -0700, Mark Knecht wrote:
Turns out I am still a bit confused about the VLOOKUP function. It
works, exactly like you suggest it should in this application, but
reading the help file I have the impression that since we didn't set
the SortOrder variable, telling calc that the table is sorted, that
VLOOKUP would return the first value that meets the criteria of an
exact match. However you say that it returns the last, which indeed
it does seem to do. It seems to say that if the exact value is not
found it will return the last value smaller than the search, but if the
exact value is found, which it should be, then it still returns the
last value? Where am I going wrong in my reading of the help file,
or is it just not really there?
Er, your comment here is ambiguous. Since we didn't provide the
SortOrder parameter, we *are* saying that the day column is
sorted. That's what you meant, isn't it?
In fact, I don't see that the help text says anywhere what happens if
there are multiple entries which match the search. The only hint I
see is that in the other, unsorted case, the function identifies "the
last value that is smaller than the criterion". Perhaps the same
sort of idea is used for sorted lists.
Assume for a moment I wanted to find the first occurrence? Can
VLOOKUP do that? (Again, assume a sort table as all mine will be.)
Interesting: not easily, I think. My first thought was simply to
reverse the source array so that the required first value would
actually come last, but that produces a list that is no longer sorted
in the way VLOOKUP() requires, of course.
Lastly, I do need to include the year. In truth, the table isn't
sorted unless I do as the 20th week (number 20 in the table) occurs
every year. When you said I might need 'combine the values' before
the VLOOKUP, what might be an efficient way to do that? I thought of
something like:
=year+((week #)/100)
which I think gives me a unique value for every row. Make sense or
is there a better/more standard way to do this?
Yes: that's an obvious technique. Alternatively (as has already been
suggested) you could concatenate the values as text strings. Note
that, if you want to keep the separate year and week columns for
appearance purposes, you could always create a new column with the
combined values (to the left of the data column) but then hide it.
At 23:44 29/05/2009 +0200, Johnny Rosenberg wrote:
You could do that. You could also do something like this:
=year&"-"&week
Like, if the year is in A1 and the week in B1, you could enter the
following into C1:
=A1&"-"&B1
So, if A1=2004 and B1=20, C1 will be 2004-20.
Yes: this is the sort of thing I had in mind, too. But sadly it
doesn't quite work. The problem, I think, is that the text string
"2004-10" sorts between "2204-1" and "2204-2", not after "2004-9", so
the column is no longer sorted properly. But you could use something like
=A1&"-"&TEXT(B1;"00")
In that case, "2004-10" does sort after "2004-09" and everything works again.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
