| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help on VLOOKUP with currencies
I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2
Then the VLOOKUP should fetch the exchange rate and put it in to other cells D6:D12 for calculations. It's not working :-( Table has 'test' values in it. Euro 4 Pound 6 SEK 6 DKK 4 NOK 5 PLN 6 VLOOKUP(B2,data!B67:C72,2) returns Euro 1 Pound 2 SEK 3 DKK 4 NOK 5 PLN 6 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
Hi Andy,
Your question is not very precise..:-) However, try this: VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) Post again in this thread if that doesn't help, and specify your data -- Kind regards, Niek Otten Microsoft MVP - Excel "AndyB" wrote in message ... | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 | Then the VLOOKUP should fetch the exchange rate and put it in to other cells | D6:D12 for calculations. | It's not working :-( | Table has 'test' values in it. | Euro 4 | Pound 6 | SEK 6 | DKK 4 | NOK 5 | PLN 6 | | VLOOKUP(B2,data!B67:C72,2) returns | Euro 1 | Pound 2 | SEK 3 | DKK 4 | NOK 5 | PLN 6 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
Hi,
I tried the formula but I get the following Euro 1 Pound 2 SEK N/A DKK N/A NOK 5 PLN 6 It seems that the formula doesn't like SKE or DKK ?? "Niek Otten" wrote: Hi Andy, Your question is not very precise..:-) However, try this: VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) Post again in this thread if that doesn't help, and specify your data -- Kind regards, Niek Otten Microsoft MVP - Excel "AndyB" wrote in message ... | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 | Then the VLOOKUP should fetch the exchange rate and put it in to other cells | D6:D12 for calculations. | It's not working :-( | Table has 'test' values in it. | Euro 4 | Pound 6 | SEK 6 | DKK 4 | NOK 5 | PLN 6 | | VLOOKUP(B2,data!B67:C72,2) returns | Euro 1 | Pound 2 | SEK 3 | DKK 4 | NOK 5 | PLN 6 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
What is your formula exactly and what is your data/are your values? B2 and B67:C72?
Why do you get a table of results; do you have a table of formulas? What formulas? What lookup values? Please be more precise -- Kind regards, Niek Otten Microsoft MVP - Excel "AndyB" wrote in message ... | Hi, | | I tried the formula but I get the following | Euro 1 | Pound 2 | SEK N/A | DKK N/A | NOK 5 | PLN 6 | | It seems that the formula doesn't like SKE or DKK ?? | | "Niek Otten" wrote: | | Hi Andy, | | Your question is not very precise..:-) | | However, try this: | | VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | | Post again in this thread if that doesn't help, and specify your data | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "AndyB" wrote in message ... | | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 | | Then the VLOOKUP should fetch the exchange rate and put it in to other cells | | D6:D12 for calculations. | | It's not working :-( | | Table has 'test' values in it. | | Euro 4 | | Pound 6 | | SEK 6 | | DKK 4 | | NOK 5 | | PLN 6 | | | | VLOOKUP(B2,data!B67:C72,2) returns | | Euro 1 | | Pound 2 | | SEK 3 | | DKK 4 | | NOK 5 | | PLN 6 | | | |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
On the 'Data' tab I have B67:B72 is a table called "currency"
The VLOOKUP uses B67:B72 with the vlues next to it in C67:C72 B67:72 C67:72 Euro 1 Pound 2 SEK 3 DKK 4 NOK 5 PLN 6 The input tab is called 'options'. It has a drop down list in cell B2, Validate list =Currency. In cell E4 I have =VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) Hope this helps "Niek Otten" wrote: What is your formula exactly and what is your data/are your values? B2 and B67:C72? Why do you get a table of results; do you have a table of formulas? What formulas? What lookup values? Please be more precise -- Kind regards, Niek Otten Microsoft MVP - Excel "AndyB" wrote in message ... | Hi, | | I tried the formula but I get the following | Euro 1 | Pound 2 | SEK N/A | DKK N/A | NOK 5 | PLN 6 | | It seems that the formula doesn't like SKE or DKK ?? | | "Niek Otten" wrote: | | Hi Andy, | | Your question is not very precise..:-) | | However, try this: | | VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | | Post again in this thread if that doesn't help, and specify your data | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "AndyB" wrote in message ... | | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 | | Then the VLOOKUP should fetch the exchange rate and put it in to other cells | | D6:D12 for calculations. | | It's not working :-( | | Table has 'test' values in it. | | Euro 4 | | Pound 6 | | SEK 6 | | DKK 4 | | NOK 5 | | PLN 6 | | | | VLOOKUP(B2,data!B67:C72,2) returns | | Euro 1 | | Pound 2 | | SEK 3 | | DKK 4 | | NOK 5 | | PLN 6 | | | |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
Hi Andy,
That works perfectly for me. I don't know what's wrong in your workbook, sorry! -- Kind regards, Niek Otten Microsoft MVP - Excel "AndyB" wrote in message ... | On the 'Data' tab I have B67:B72 is a table called "currency" | The VLOOKUP uses B67:B72 with the vlues next to it in C67:C72 | B67:72 C67:72 | Euro 1 | Pound 2 | SEK 3 | DKK 4 | NOK 5 | PLN 6 | | The input tab is called 'options'. | It has a drop down list in cell B2, Validate list =Currency. | In cell E4 I have =VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | | Hope this helps | | | "Niek Otten" wrote: | | What is your formula exactly and what is your data/are your values? B2 and B67:C72? | Why do you get a table of results; do you have a table of formulas? What formulas? What lookup values? | | Please be more precise | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | | "AndyB" wrote in message ... | | Hi, | | | | I tried the formula but I get the following | | Euro 1 | | Pound 2 | | SEK N/A | | DKK N/A | | NOK 5 | | PLN 6 | | | | It seems that the formula doesn't like SKE or DKK ?? | | | | "Niek Otten" wrote: | | | | Hi Andy, | | | | Your question is not very precise..:-) | | | | However, try this: | | | | VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | | | | Post again in this thread if that doesn't help, and specify your data | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | "AndyB" wrote in message ... | | | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 | | | Then the VLOOKUP should fetch the exchange rate and put it in to other cells | | | D6:D12 for calculations. | | | It's not working :-( | | | Table has 'test' values in it. | | | Euro 4 | | | Pound 6 | | | SEK 6 | | | DKK 4 | | | NOK 5 | | | PLN 6 | | | | | | VLOOKUP(B2,data!B67:C72,2) returns | | | Euro 1 | | | Pound 2 | | | SEK 3 | | | DKK 4 | | | NOK 5 | | | PLN 6 | | | | | | | | | |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
I prefer answers via these groups, because others may benefit.
Now that nobody seems to have any additional ideas, you may mail me your workbook so I can have a look, if you wish -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... | Hi Andy, | | That works perfectly for me. I don't know what's wrong in your workbook, sorry! | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | | "AndyB" wrote in message ... || On the 'Data' tab I have B67:B72 is a table called "currency" || The VLOOKUP uses B67:B72 with the vlues next to it in C67:C72 || B67:72 C67:72 || Euro 1 || Pound 2 || SEK 3 || DKK 4 || NOK 5 || PLN 6 || || The input tab is called 'options'. || It has a drop down list in cell B2, Validate list =Currency. || In cell E4 I have =VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) || || Hope this helps || || || "Niek Otten" wrote: || || What is your formula exactly and what is your data/are your values? B2 and B67:C72? || Why do you get a table of results; do you have a table of formulas? What formulas? What lookup values? || || Please be more precise || || -- || Kind regards, || || Niek Otten || Microsoft MVP - Excel || || || || "AndyB" wrote in message ... || | Hi, || | || | I tried the formula but I get the following || | Euro 1 || | Pound 2 || | SEK N/A || | DKK N/A || | NOK 5 || | PLN 6 || | || | It seems that the formula doesn't like SKE or DKK ?? || | || | "Niek Otten" wrote: || | || | Hi Andy, || | || | Your question is not very precise..:-) || | || | However, try this: || | || | VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) || | || | Post again in this thread if that doesn't help, and specify your data || | || | -- || | Kind regards, || | || | Niek Otten || | Microsoft MVP - Excel || | || | "AndyB" wrote in message ... || | | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 || | | Then the VLOOKUP should fetch the exchange rate and put it in to other cells || | | D6:D12 for calculations. || | | It's not working :-( || | | Table has 'test' values in it. || | | Euro 4 || | | Pound 6 || | | SEK 6 || | | DKK 4 || | | NOK 5 || | | PLN 6 || | | || | | VLOOKUP(B2,data!B67:C72,2) returns || | | Euro 1 || | | Pound 2 || | | SEK 3 || | | DKK 4 || | | NOK 5 || | | PLN 6 || | || | || | || || || | | |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
Hello Niek,
I've sent the workbook via email. "Niek Otten" wrote: I prefer answers via these groups, because others may benefit. Now that nobody seems to have any additional ideas, you may mail me your workbook so I can have a look, if you wish -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... | Hi Andy, | | That works perfectly for me. I don't know what's wrong in your workbook, sorry! | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | | "AndyB" wrote in message ... || On the 'Data' tab I have B67:B72 is a table called "currency" || The VLOOKUP uses B67:B72 with the vlues next to it in C67:C72 || B67:72 C67:72 || Euro 1 || Pound 2 || SEK 3 || DKK 4 || NOK 5 || PLN 6 || || The input tab is called 'options'. || It has a drop down list in cell B2, Validate list =Currency. || In cell E4 I have =VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) || || Hope this helps || || || "Niek Otten" wrote: || || What is your formula exactly and what is your data/are your values? B2 and B67:C72? || Why do you get a table of results; do you have a table of formulas? What formulas? What lookup values? || || Please be more precise || || -- || Kind regards, || || Niek Otten || Microsoft MVP - Excel || || || || "AndyB" wrote in message ... || | Hi, || | || | I tried the formula but I get the following || | Euro 1 || | Pound 2 || | SEK N/A || | DKK N/A || | NOK 5 || | PLN 6 || | || | It seems that the formula doesn't like SKE or DKK ?? || | || | "Niek Otten" wrote: || | || | Hi Andy, || | || | Your question is not very precise..:-) || | || | However, try this: || | || | VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) || | || | Post again in this thread if that doesn't help, and specify your data || | || | -- || | Kind regards, || | || | Niek Otten || | Microsoft MVP - Excel || | || | "AndyB" wrote in message ... || | | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 || | | Then the VLOOKUP should fetch the exchange rate and put it in to other cells || | | D6:D12 for calculations. || | | It's not working :-( || | | Table has 'test' values in it. || | | Euro 4 || | | Pound 6 || | | SEK 6 || | | DKK 4 || | | NOK 5 || | | PLN 6 || | | || | | VLOOKUP(B2,data!B67:C72,2) returns || | | Euro 1 || | | Pound 2 || | | SEK 3 || | | DKK 4 || | | NOK 5 || | | PLN 6 || | || | || | || || || | | |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
Please post our findings, so others may benefit
-- Kind regards, Niek Otten Microsoft MVP - Excel "AndyB" wrote in message ... | Hello Niek, | | I've sent the workbook via email. | | "Niek Otten" wrote: | | I prefer answers via these groups, because others may benefit. | Now that nobody seems to have any additional ideas, you may mail me your workbook so I can have a look, if you wish | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Niek Otten" wrote in message ... | | Hi Andy, | | | | That works perfectly for me. I don't know what's wrong in your workbook, sorry! | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | | | | | "AndyB" wrote in message ... | || On the 'Data' tab I have B67:B72 is a table called "currency" | || The VLOOKUP uses B67:B72 with the vlues next to it in C67:C72 | || B67:72 C67:72 | || Euro 1 | || Pound 2 | || SEK 3 | || DKK 4 | || NOK 5 | || PLN 6 | || | || The input tab is called 'options'. | || It has a drop down list in cell B2, Validate list =Currency. | || In cell E4 I have =VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | || | || Hope this helps | || | || | || "Niek Otten" wrote: | || | || What is your formula exactly and what is your data/are your values? B2 and B67:C72? | || Why do you get a table of results; do you have a table of formulas? What formulas? What lookup values? | || | || Please be more precise | || | || -- | || Kind regards, | || | || Niek Otten | || Microsoft MVP - Excel | || | || | || | || "AndyB" wrote in message ... | || | Hi, | || | | || | I tried the formula but I get the following | || | Euro 1 | || | Pound 2 | || | SEK N/A | || | DKK N/A | || | NOK 5 | || | PLN 6 | || | | || | It seems that the formula doesn't like SKE or DKK ?? | || | | || | "Niek Otten" wrote: | || | | || | Hi Andy, | || | | || | Your question is not very precise..:-) | || | | || | However, try this: | || | | || | VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | || | | || | Post again in this thread if that doesn't help, and specify your data | || | | || | -- | || | Kind regards, | || | | || | Niek Otten | || | Microsoft MVP - Excel | || | | || | "AndyB" wrote in message ... | || | | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 | || | | Then the VLOOKUP should fetch the exchange rate and put it in to other cells | || | | D6:D12 for calculations. | || | | It's not working :-( | || | | Table has 'test' values in it. | || | | Euro 4 | || | | Pound 6 | || | | SEK 6 | || | | DKK 4 | || | | NOK 5 | || | | PLN 6 | || | | | || | | VLOOKUP(B2,data!B67:C72,2) returns | || | | Euro 1 | || | | Pound 2 | || | | SEK 3 | || | | DKK 4 | || | | NOK 5 | || | | PLN 6 | || | | || | | || | | || | || | || | | | | | | | |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
Probably superfluous, but anyway:
<On the 'Data' tab I have B67:B72 is a table called "currency" No. It's A67:A72 -- Kind regards, Niek Otten Microsoft MVP - Excel "AndyB" wrote in message ... | On the 'Data' tab I have B67:B72 is a table called "currency" | The VLOOKUP uses B67:B72 with the vlues next to it in C67:C72 | B67:72 C67:72 | Euro 1 | Pound 2 | SEK 3 | DKK 4 | NOK 5 | PLN 6 | | The input tab is called 'options'. | It has a drop down list in cell B2, Validate list =Currency. | In cell E4 I have =VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | | Hope this helps | | | "Niek Otten" wrote: | | What is your formula exactly and what is your data/are your values? B2 and B67:C72? | Why do you get a table of results; do you have a table of formulas? What formulas? What lookup values? | | Please be more precise | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | | "AndyB" wrote in message ... | | Hi, | | | | I tried the formula but I get the following | | Euro 1 | | Pound 2 | | SEK N/A | | DKK N/A | | NOK 5 | | PLN 6 | | | | It seems that the formula doesn't like SKE or DKK ?? | | | | "Niek Otten" wrote: | | | | Hi Andy, | | | | Your question is not very precise..:-) | | | | However, try this: | | | | VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | | | | Post again in this thread if that doesn't help, and specify your data | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | "AndyB" wrote in message ... | | | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 | | | Then the VLOOKUP should fetch the exchange rate and put it in to other cells | | | D6:D12 for calculations. | | | It's not working :-( | | | Table has 'test' values in it. | | | Euro 4 | | | Pound 6 | | | SEK 6 | | | DKK 4 | | | NOK 5 | | | PLN 6 | | | | | | VLOOKUP(B2,data!B67:C72,2) returns | | | Euro 1 | | | Pound 2 | | | SEK 3 | | | DKK 4 | | | NOK 5 | | | PLN 6 | | | | | | | | | |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help on VLOOKUP with currencies
Neik,
Further to previos posts, the original formula actually works, VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) I had a couple of spaces in my table before DKK and SEK Because I uesd 1 list for my validation and another for the VLOOKUP I needed to have exactly the same text in both lists. Thanks again AndyB "Niek Otten" wrote: Probably superfluous, but anyway: <On the 'Data' tab I have B67:B72 is a table called "currency" No. It's A67:A72 -- Kind regards, Niek Otten Microsoft MVP - Excel "AndyB" wrote in message ... | On the 'Data' tab I have B67:B72 is a table called "currency" | The VLOOKUP uses B67:B72 with the vlues next to it in C67:C72 | B67:72 C67:72 | Euro 1 | Pound 2 | SEK 3 | DKK 4 | NOK 5 | PLN 6 | | The input tab is called 'options'. | It has a drop down list in cell B2, Validate list =Currency. | In cell E4 I have =VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | | Hope this helps | | | "Niek Otten" wrote: | | What is your formula exactly and what is your data/are your values? B2 and B67:C72? | Why do you get a table of results; do you have a table of formulas? What formulas? What lookup values? | | Please be more precise | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | | "AndyB" wrote in message ... | | Hi, | | | | I tried the formula but I get the following | | Euro 1 | | Pound 2 | | SEK N/A | | DKK N/A | | NOK 5 | | PLN 6 | | | | It seems that the formula doesn't like SKE or DKK ?? | | | | "Niek Otten" wrote: | | | | Hi Andy, | | | | Your question is not very precise..:-) | | | | However, try this: | | | | VLOOKUP(B2,data!$B$67:$C$72,2,FALSE) | | | | Post again in this thread if that doesn't help, and specify your data | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | "AndyB" wrote in message ... | | | I'm trying to do a VLOOKUP where the user chooses a currency in one cell B2 | | | Then the VLOOKUP should fetch the exchange rate and put it in to other cells | | | D6:D12 for calculations. | | | It's not working :-( | | | Table has 'test' values in it. | | | Euro 4 | | | Pound 6 | | | SEK 6 | | | DKK 4 | | | NOK 5 | | | PLN 6 | | | | | | VLOOKUP(B2,data!B67:C72,2) returns | | | Euro 1 | | | Pound 2 | | | SEK 3 | | | DKK 4 | | | NOK 5 | | | PLN 6 | | | | | | | | | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Identifying currencies | Excel Discussion (Misc queries) | |||
| reconciliation in different currencies | Excel Discussion (Misc queries) | |||
| Different currencies | Excel Discussion (Misc queries) | |||
| How to handle multiple currencies with one spreadsheet | Excel Discussion (Misc queries) | |||
| different currencies | Excel Worksheet Functions | |||
Linear Mode
