2009/5/30 Brian Barker <[email protected]>: > 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
He he he… didn't think of that, sorry for misleading. And thanks for the TEXT function, I didn't know it existed, or at least what it does… I usually do the same thing in a bit more complicated way, adding the string to some zeroes and then only keep the x last characters using the RIGHT function, but this TEXT function seems to be what I was looking for all the time in a much easier way, and you just provided it to me, and I didn't even ask… :D Thanks! Kind regards Johnny Rosenberg > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
