retrieving value from a chart/matrix through input references?

[enternetjohan]

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]

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

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

 

[enternetjohan]

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]

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]

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]

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]

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]

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]

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