retrieving value from a chart/matrix through input references?

enternetjohan

New Member
Joined
Sep 14, 2015
Messages
12
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
 

enternetjohan

New Member
Joined
Sep 14, 2015
Messages
12
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
Joined
Sep 11, 2015
Messages
350
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
Joined
Sep 14, 2015
Messages
12
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
Joined
Sep 11, 2015
Messages
350
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
Joined
Sep 14, 2015
Messages
12
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
Joined
Sep 14, 2015
Messages
12
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
Joined
Sep 11, 2015
Messages
350
Ah that makes sense. Sorry I guess I put your inputs backwards from what you said. Glad you got it figured out! :)
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top