Vlookup w/sum?
 

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

Vlookup w/sum?
 

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


"Jeanette" wrote in message
...
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

Vlookup w/sum?
 

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.

"Bernie Deitrick" wrote:

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


"Jeanette" wrote in message
...
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

Vlookup w/sum?
 

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


"Jeanette" wrote in message
...
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.

"Bernie Deitrick" wrote:

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


"Jeanette" wrote in message
...
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