And lo On Mon, 26 Jan 2009 13:51:14 -0000, scott <sco### [at] scottcom> did  
spake thusly:

>>> The lookup() function only works if the list you are looking up in is  
>>> sorted in ascending order.  By sorting the cells by column B,  
>>> concatenate(a1:a9,b1:b9) is no longer is ascending order.
>>
>> The question is...why?  What use is something that looks things up that  
>> requires you do its work for it first?
>
> It's because lookup always returns a "closest match" result, even if it  
> can't match exactly.
>
> Use hlookup or vlookup instead, it lets you set a flag which will only  
> return exact matches, then the list does not need to be ordered.

Except you wouldn't be able to use the concatenate function with either of  
those as they require the answer range to be included within the search  
range ie. vlookup("ac", A1:C9, 3, False) would return nothing as no value  
of "ac" exists within A1:A9 and you can't wedge the combined values into a  
range. However if you created another column of concatenated values at C  
with the results now moved to D then vlookup("ac", C1:D9, 2, False) would  
return 4 regardless of sort.

Without creating a new column then perhaps only  
=INDEX(C1:C9,MATCH("AC",CONCATENATE(A1:A9,B1:B9),0),1) entered as a array  
formula would work, but yes I agree it is clumsy.

-- 
Phil Cook

--
I once tried to be apathetic, but I just couldn't be bothered
http://flipc.blogspot.com

Post a reply to this message