# retrieving value from a chart/matrix through input references?

#### enternetjohan

##### New Member
Hello,

I have a chart containing 5 columns (A to E) and 10 rows (1 to 10).

Row 1 contains the column headlines: A = Dates, B = Currency1, C = Currency2, D = Currency3 and E = Currency4.

Column A, row 2 to 10, contains dates, 2009-01-01, 2009-01-02 etc.

Column B to E, row 2 to 10, contains exchange rates to SEK for each currency and for each date.

I have two cells, K2 and K3, with manual input reference values.
K2 will contain a currency (here a desired currency is inputed, for example "Currency1").
K3 will contain a date (here a desired date is inputed, for example "2009-01-01").

Now, I want to create a formula in cell K4 that, given a desired currency and a desired date, returns the exchange rate from the chart for that currency and that date.

It sounded so easy in theory but turned out to be so impossible in reality Hope someone here can help!

Ps. I am from Sweden so please excuse any poor english.

/ J

#### LockeGarmin

##### Active Member
Give this formula a try in K4 and let me know if it worked.

Code:
``=INDEX(B2:E10,MATCH(K2,A2:A10,0),MATCH(K3,B1:E1,0))``

Last edited:

#### enternetjohan

##### New Member
Thanks. I did try your formula. Unfortunately I can not get it to work. I had to adapt it to my version of excel (MS Office 2010). Afterwards it looked like this:

=INDEX(B2:E10;MATCH(K2;A2:A10;0);MATCH(K3;B1:E1;0))

It returns a #name? error (freely translated from swedish #namn?).
If I switch the english formula entrys for swedish (MATCH --> PASSA) I get a #missing! (freely translated from #saknas!).

Maybe it has to do with cell configurations?
The whole of column A is configured as Dates.
The whole of column B to E is configured as numbers.
Cell K2 as number.
Cell K3 as date.
Cell K4 as general.

#### LockeGarmin

##### Active Member
Hmm, well I don't have the Swedish version of Excel so it will be pretty difficult to troubleshoot the issue. I'm not for sure what the #MISSING! error would be though I'm going to guess it's supposed to be #N/A in the English version. Changing the functions to PASSA was how you to overcome the #NAME error. One of your Match (Passa) functions are having trouble finding a match. Are you actually putting in "Currency1" in K2 or are you just putting "1" (number)? Since you said K2 is a number I'm guessing that you aren't entering "Currency1" and that's what's causing the error.

#### enternetjohan

##### New Member
I am inputing "Currency1" into K2 and "2009-01-01" into K3 (as given in the example above) and making sure the spelling is correct.

I have played around with the cell formatting, trying different formats. Nothing works, still get the #name issue.

I have tried making a new chart, where all cells are formatted in general, which also doesn't work.

The english versions of formulas does work with the swedish excel version so this should not be the problem.

Maybe the error lies elsewhere? Did you get it to work in your excel? What formats did you use if so?

Hmm, well I don't have the Swedish version of Excel so it will be pretty difficult to troubleshoot the issue. I'm not for sure what the #MISSING! error would be though I'm going to guess it's supposed to be #N/A in the English version. Changing the functions to PASSA was how you to overcome the #NAME error. One of your Match (Passa) functions are having trouble finding a match. Are you actually putting in "Currency1" in K2 or are you just putting "1" (number)? Since you said K2 is a number I'm guessing that you aren't entering "Currency1" and that's what's causing the error.

#### LockeGarmin

##### Active Member
I tested the formula before posting so I know it works alright in the English version. In terms of formatting I used text in K2 and Date in K3.

Sadly I'm not sure there is anything else I can do. Getting the #NAME! error again has me pretty stumped. You may have more luck with your problem if you post your question in the other languages forum on this site and maybe someone with the Swedish version of excel will be able to help. Sorry!

Questions in Other Languages

#### enternetjohan

##### New Member
Ok, I will try that. Thanks for your help!

One question though; What did you format the chart as, i.e. Column A to E?
Column A = Dates?
Column B to E = Numbers?
Any special format for row 1? (containing the column headlines "Dates" and so on...).

I tested the formula before posting so I know it works alright in the English version. In terms of formatting I used text in K2 and Date in K3.

Sadly I'm not sure there is anything else I can do. Getting the #NAME! error again has me pretty stumped. You may have more luck with your problem if you post your question in the other languages forum on this site and maybe someone with the Swedish version of excel will be able to help. Sorry!

Questions in Other Languages

#### enternetjohan

##### New Member
Ok, problem solved!

I had to switch cells K2 and K3 in the formula to arrive at the correct match against date or currency. Se below.

=INDEX(B2:E10;MATCH(K3;A2:A10;0);MATCH(K2;B1:E1;0))

Swedish formula:

=INDEX(B2:E10;PASSA(K3;A2:A10;0);PASSA(K2;B1:E1;0))

Thanks for all your help!

#### LockeGarmin

##### Active Member
Ah that makes sense. Sorry I guess I put your inputs backwards from what you said. Glad you got it figured out!

1,082,389
Messages
5,365,161
Members
400,827
Latest member
Mitzescu

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...