Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |