Forum Discussion
data tables & index/match function
As of yesterday, when I create a data table within a Excel 365 file & use that table within an index/match function, when I hit enter, the formula is looking for an external source & never had to do ...
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.
=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 apologize, I did not see that attachment, I was too focused on my spreadsheet. I hadn't seen that way to create a formula for the rows. Thank you for your time.