ah. That works a treat! thanks for the info on sorting too. Christian On Mon, 2005-08-29 at 10:50 -0400, Anthony Chilco wrote: > Hi Christian, > Sorry, the period was a typo. The formula should be: > > =IF($G44="";"";$H44/VLOOKUP($G44;$'currency rates'.$A$2:$B$5;2;false)) > > Without the 'false' parameter, vlookup expects the list to be sorted. > That's why USD followed by SKR works. Having 'I' follow 'S' won't. If > you change the period to a semicolon it will work. Debugging complicated > formulae is quite easy if you use the autopilot. It's the rectangular > button to the left of the edit box. > > Christian de Larrinaga wrote: > > >Anthony > > > >Thanks for the suggestion but .false returns a #NAME? error. (I've not > >as yet analysed why this is happening). > > > >But for the purposes of clarifying further my application. > > > >In the instance that I am using VLOOKUP the calculation is looking up an > >alphanumeric (text) string to return a number from a very simple table. > > > >It works fine for the first two table entries; so for example. > > > >the table with > > > >currency.code exchange.rate > >USD 1.89 > >SKR 5.50 > >ISK 198.09 > > > >Vlookup looks up "USD" and returns 1.89 which is then used to calculate > >an amount paid in USD (US Dollars) into say GBP (Pounds Sterling) for > >accounting purposes. > > > >Vlookup will also look up SKR and return 5.50 correctly. > > > >But it returns N/A for ISK. (or any other third row value or greater row > >in the table.) > > > >What is curious is, as I mentioned before. this very same formula with > >VLOOKUP works across much larger tables of exactly the same construction > >(than just 2 rows) in MS Excel. i.e., In Excel it will in the above > >example return the 5.50 for the lookup value of SKR. > > > > > >Christian > ># > > > > > > > >#On Mon, 2005-08-29 at 09:09 -0400, Anthony Chilco wrote: > > > > > >>Hi Christian, > >>Please don't reply directly to anyone who answers your questions. Others > >>on the list may be able to help or may benefit from the thread. > >> > >>For an exact match, add 'false' as the last parameter: > >>=IF($G44="";"";$H44/VLOOKUP($G44;$'currency rates'.$A$2:$B$5;2.false)) > >>I'm assuming that the lookup data is in rows one to four. If that still > >>doesn't work the problem may be the format of the search or vlookup > >>data. If the search is text '3', and the lookup in a number (or vice > >>versa) you'll have trouble. Forcing the search data to numeric with > >>Value($G44), or to text Text($G44;"0") may help. > >>tc > >> > >> > >>Christian de Larrinaga wrote: > >> > >> > >> > >>>Anthony > >>> > >>>Thanks hugely for the feedback but I do want an exact match. > >>> > >>>I don't understand what you are suggesting I alter in > >>> > >>>=IF($G44="";"";$H44/VLOOKUP($G44;$'currency rates'.$A$2:$B$5;2)) ? > >>> > >>>At the moment I get the correct lookup for the first two (and only the > >>>first two) entries in the lookup table. The problem is it won't lookup > >>>any further entries. > >>> > >>>So if I have the lookup table below: > >>> > >>>currency exchange rate > >>>1 1.25 > >>>2 2.34 > >>>3 1550.03 > >>> > >>>Then the lookup will find 1 and return 1.25, or 2 and return 2.34 but > >>>for currency code 3 (etc) it returns N/A. > >>> > >>>As I indicated below. The formula as it stands works fine in Excel. > >>> > >>>This has been bemusing me so I am very grateful if you can suggest a > >>>revised formula. > >>> > >>>best regards > >>> > >>>Christian > >>> > >>> > >>> > >>>On Sat, 2005-08-27 at 15:57 -0400, Anthony Chilco wrote: > >>> > >>> > >>> > >>> > >>>>Hi Christian, > >>>>You're not specifying the last parameter of the vlookup function. It is > >>>>'true' or 'false', 'false' meaning to look for an exact match. By > >>>>default, it is 'true'. > >>>>tc > >>>> > >>>>Christian de Larrinaga wrote: > >>>> > >>>> > >>>> > >>>> > >>>> > >>>>>I have a curious issue that looks like a bug with VLOOKUP. > >>>>> > >>>>>I have a spreadsheet that is a multi column list of expense items. > >>>>>In a support sheet I have a small two column table of currency and > >>>>>conversion rates. > >>>>> > >>>>>I am using VLOOKUP to determine the currency code in the first > >>>>>worksheetfor the expense item and return the appropriate conversion rate > >>>>>in a supporting worksheet to the first worksheet to use this to bring > >>>>>all expenses in the first sheet into the same accounting currency. > >>>>> > >>>>>The formula works 100% in Excel. But in Open Office it returns only two > >>>>>currency conversions correctly. Applied to a third currency onwards in > >>>>>the supporting worksheet it provides a N/A back to the first sheet. > >>>>> > >>>>>The VLOOKUP Formula in the first sheet is > >>>>>=IF($G44="";"";$H44/VLOOKUP($G44;$'currency rates'.$A$2:$B$5;2)) > >>>>> > >>>>>where $Gxx is the Currency code, Hxx the amount in that currency, > >>>>>'currency rates' the name of the worksheet containing the list of > >>>>>currency rates and $A$2:$B$5;2 the range in the currency rate worksheet > >>>>>to find the correct rate for that currency. > >>>>> > >>>>>I am using Openoffice.org 2.0-pre build 1.9.79.2.3 Novell Edition. > >>>>> > >>>>>Has anybody experienced this or have a workaround? > >>>>> > >>>>>Christian > >>>>> > >>>>> > >>>>>--------------------------------------------------------------------- > >>>>>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]
