Thanks This solution partly worked
LOOKUP returned the appropriate answer where the concatenated value (C38 in this example) matched a value in the 1st RawData Column However when no value matched it returned the cell for the nearest matching value rather than 0 For example when search criterion = 5114 Search vector = 5114 Result vector = 700 Result in cell is 700 (correct) But when search criterion = 5115 Search vector = not present Result vector = not present (but 5114 is 700) Result in cell is 700 (which I think is incorrect) Any way round this. I seem to recall this sort of thing is/was a problem with Excel and is part of the reason I switched in the first place. Benedict > -----Original Message----- > From: Anthony Chilco [mailto:[EMAIL PROTECTED] > Sent: 29 March 2005 18:48 > To: users@openoffice.org > Subject: Re: [users] Calc Ooo 2 beta how can I make LOOKUP > work with CONCATENATE > > Hi Benedict, > I haven't installed 2beta yet so I can't test this. I use a > lot of lookups and concatenates and get errors many times > when the data types don't match: i.e. I'm searching a list of > text using a number as the criterion or vice versa. This I > usually solve by adding the 'value' or 'text' function around > the search criterion like this: > > =LOOKUP(value($C83);RawD > or > =LOOKUP(text($C83;more params.....);RawD tc > > > Benedict Leigh wrote: > > I was using Ooo 1.4 where a cell created using concatenate (from 2 > > cells) could be used as the source of a lookup to return a > value from > > another page in the spreadsheet. > > > > This now seems to work differently in Ooo 2 beta . > > > > I tried saving as ods, and re-entering the formula, this > had no effect. > > > > Entering the number that the concatenate function should > return in the > > appropriate cell resulted in the formula working - but I > don't want to > > do this for the approx 2500 cells I would need to. > > > > The formula for the concatenate cell looks like this > > =CONCATENATE($A$2;A83) > > > > And the formula for the lookup cell like this (where C83 is the > > concatenate > > cell) =LOOKUP($C83;RawData.$A$5:$A$505;RawData.E$5:E$505) > > > > Is there a solution to this - or will I need to go back to > using Ooo > > 1.4 > > > > Thanks in advance > > Benedict > > > > > > > --------------------------------------------------------------------- > > 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] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
