Hi All,

I am trying to come up with a spread sheet that would convert amount in
certain foreign currency to local currency equivalent and show final value
in local currency only if there are input for both foreign currency
(e.g.USD, EUR) and amount in 2 separate adjacent cells. The output in
another cell for the local currency equivalent should not display any thing
if both datas are NOT present. Think the nested IF function combined with
VLookup can do this trick.

So far, I have only managed below Vlookup formula and the cell display #NA
which is not desirable. I would like it to be blank. Just couldn't work out
the correct nested IF to combine into below Vlookup.

=VLOOKUP('Work Sheet'!F4,'Currency Exchange Rating'!A1:D5,2)

In sheet Currency Exchange Rate are the existing Exchange rate which is
updated weekly.

The results are calculated and displayed in Work Sheet.

Any help would appreciated here ... Thnks in advance.

If you wan't to avoid the N/A during you're lookup and
only give an answer when the lookup finds something...

=if(isna(VLOOKUP('Work Sheet'!F4,'Currency Exchange
Rating'!A1:D5,2)),"",VLOOKUP('Work Sheet'!F4,'Currency
Exchange Rating'!A1:D5,2))

this gives you a blank when there is nothing to lookup -
you can change the "" to 0 if you'd rather have it display
a zero.

hth,
Dave



-----Original Message-----
Hi All,

I am trying to come up with a spread sheet that would
convert amount in
certain foreign currency to local currency equivalent and
show final value
in local currency only if there are input for both
foreign currency
(e.g.USD, EUR) and amount in 2 separate adjacent cells.
The output in
another cell for the local currency equivalent should not
display any thing
if both datas are NOT present. Think the nested IF
function combined with
VLookup can do this trick.

So far, I have only managed below Vlookup formula and the
cell display #NA
which is not desirable. I would like it to be blank. Just
couldn't work out
the correct nested IF to combine into below Vlookup.

=VLOOKUP('Work Sheet'!F4,'Currency Exchange Rating'!
A1:D5,2)

In sheet Currency Exchange Rate are the existing Exchange
rate which is
updated weekly.

The results are calculated and displayed in Work Sheet.

Any help would appreciated here ... Thnks in advance.


.