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]
