Vlookup w/sum?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeanette - 26 Oct 2006 16:44 GMT

I am working on a spreadsheet where I need to find the value of the product
returned in the Vlookup * another number, how do I get this formula to work
properly?

I currently have
=+IF(VLOOKUP($B$9,E7:G19,3)*$B$8,VLOOKUP($B$9,$E$7:$G$19,3)*$B$8,"N/A"), this
is supposed to bring back the value of $60 converted into pounds at a rate of
0.6200, the actual number is comming back much higher at $626.79, in reality
is should be $37.20.

The lookup table is set up as follows

column E is the Country to match in B7
Column F is the Currency to match in B9
Column G is the exchange rate of the Currency in Column F

In the end, I need a formula that will look at B9, find the currency rate in
the vlookup table and bring back the value of the currency, then times it by
the $$ being exchanged in B8

Column E Column F Column G
Country    Currency    Exchange Rate
Argentina    Peso    3.3500
Canada    Dollar    1.5603
England    Pound    0.6200
France    Euro    0.9525
Germany    Euro    0.9526
Hong Kong    Dollar    7.7980
India    Rupee    47.8401
Israel    Shekel    4.7783
Japan    Yen    119.0550
Mexico    Peso    10.4465
Russia    Rubles    31.8300
South Korea    Won    1,185.8000
Venezuela    Bolivar    1,603.7500

Reply to this Message

Bernie Deitrick - 26 Oct 2006 19:12 GMT

Jeanette,

Try:

=IF(NOT(ISERROR(VLOOKUP($B$9,$E$7:$G$19,3,False))),VLOOKUP($B$9,$E$7:$G$19,3,False)*$B$8,"N/A")

HTH,
Bernie
MS Excel MVP

>I am working on a spreadsheet where I need to find the value of the product
> returned in the Vlookup * another number, how do I get this formula to work
[quoted text clipped - 31 lines]
> South Korea Won 1,185.8000
> Venezuela Bolivar 1,603.7500

Reply to this Message

Jeanette - 26 Oct 2006 19:26 GMT

Thanks Bernie, I tried that and it brought back N/A only, no figure, I need
the formula to figure $$ to exchange if the value is True and N/A if the cell
is blank or false.

> Jeanette,
>
[quoted text clipped - 41 lines]
> > South Korea Won 1,185.8000
> > Venezuela Bolivar 1,603.7500

Reply to this Message

Bernie Deitrick - 27 Oct 2006 13:45 GMT

Jeanette,

The formula returned N/A because there was no match between the value that you were looking up (from
cell B9) and the first column of the data table (E7:E19).

What value are you trying to find? Is it in the first column of your lookup table? Exactly?

HTH,
Bernie
MS Excel MVP

> Thanks Bernie, I tried that and it brought back N/A only, no figure, I need
> the formula to figure $$ to exchange if the value is True and N/A if the cell
[quoted text clipped - 45 lines]
>> > South Korea Won 1,185.8000
>> > Venezuela Bolivar 1,603.7500

Reply to this Message