Barrie Thanks
This worked perfectly, once I realised I should change the index number to get the appropriate column. Benedict > -----Original Message----- > From: Barrie Backhurst [mailto:[EMAIL PROTECTED] > Sent: 29 March 2005 23:17 > To: users@openoffice.org > Subject: Re: [users] Calc Ooo 2 beta how can I make LOOKUP > work with CONCATENATE > > On Tuesday 29 March 2005 18:13, Benedict Leigh wrote: > > 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 > > > > If you use VLOOKUP, it has a Boolean option for Sort order > and if you set this to false, it will return #N/A if no match > is found. If zero is required to be returned then the ISNA > function can be used within an IF function. > > Using your examples: > > =LOOKUP($C83;RawData.$A$5:$E$505;5;0) would return #N/A if no match > > =IF(ISNA(VLOOKUP($C83;RawData.$A$5:$E$505;5;0));0;VLOOKUP($C83 > ;RawData.$A$5: > $E$505;5;0)) would return 0 if no match > > HTH > > Barrie > > --------------------------------------------------------------------- > 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]
