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]
