Help on VLOOKUP with currencies

A

AndyB

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
 
N

Niek Otten

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

| 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
 
A

AndyB

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 ??
 
N

Niek Otten

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



| 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
| >
| > | 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
| >
| >
| >
 
A

AndyB

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
 
N

Niek Otten

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



| 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
| >
| >
| >
| > | 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
| > | >
| > | > | 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
| > | >
| > | >
| > | >
| >
| >
| >
 
N

Niek Otten

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

| 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
|
|
|
|| 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
|| >
|| >
|| >
|| > | 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
|| > | >
|| > | > | 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
|| > | >
|| > | >
|| > | >
|| >
|| >
|| >
|
|
 
A

AndyB

Hello Niek,

I've sent the workbook via email.

Niek Otten said:
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

| 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
|
|
|
|| 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
|| >
|| >
|| >
|| > | 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
|| > | >
|| > | > | 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
|| > | >
|| > | >
|| > | >
|| >
|| >
|| >
|
|
Click to expand...
 
N

Niek Otten

Please post our findings, so others may benefit

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 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
| >
| > | 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
| > |
| > |
| > |
| > || 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
| > || >
| > || >
| > || >
| > || > | 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
| > || > | >
| > || > | > | 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
| > || > | >
| > || > | >
| > || > | >
| > || >
| > || >
| > || >
| > |
| > |
| >
| >
| >
 
N

Niek Otten

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



| 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
| >
| >
| >
| > | 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
| > | >
| > | > | 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
| > | >
| > | >
| > | >
| >
| >
| >
 
A

AndyB

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup w/sum? 3
Need Help with VLOOKUP 4
Return value from column 2
Alternative to IF statements (Convert currencies…) 2
help with formula 0
VLOOKUP Errors with Date formats 1
Issue with VLOOKUP 3
Problem working with Vlookup 3

Top