Forum Discussion
data tables & index/match function
HansVogelaar Hello all! I wasn't aware you (rservice0320) had re-posted the question and to avoid duplicates, I have merged these two threads. Sorry for any confusion!
See the attached version. I hope the formulas in it do what you want. you'll see that they are structured differently from what you had,
The formulas in B6 and B7 on the KPI Dashboard sheet make no sense. For example
=INDEX(Table1!$C$2:$E$17,MATCH(1*(Table1!$A$2:$A$17=$A$7)*(Table1!$B$2:$B$17=$A$5)*(Table1!$C$1:$E$1=$B$5),0))
refers to a worksheet named Table1, but there is no such sheet. There is a table of that name, but you cannot refer to it that way.
But even if you replace Table1 with 'kpi Table' or 'kpi Database', the formulas won't return a useful result.
- well, this formula results in a spill error:
=INDEX('kpi Database'!$C$2:$E$17,MATCH(1,'kpi Database'!$A$2:$A$17=$A$7)*('kpi Database'!$B$2:$B$17=A$5)*('kpi Database'!$C$1:$E$1=$B$5),0)
is there some reason why the formula won't read the years in KPI database (in the rows C1:E1) vs. everything else is in columnar form. I would think the formula should be able to find it & match it. maybe I need to include another index so it reads the rows? The resulting value should be 250 for "CDF Vol for 2019". Sorry this is causing such agita, but I am been working on this for couple of hours.Did you take the trouble to look at the workbook that I attached?
- I do not see a drop down menu to remove this "best response".
rservice0320 Hello! Here's the image of how you remove a best response.
Of course, only the author of the original post (such as yourself in this case) can mark best response or remove it, along with MVPs and employees.
I hope that helps for the future!
