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]
