0

I have a worksheet in which I am looking to utilize the Index/Matching formula to pull in exact values for the unique intersection of Department & Account values on a separate data sheet.

However I need the Index Array to be flexible as I'm looking for the same "unique" intersection of department & account values across multiple columns (columns represent months in my data sheet).

I've tried to use an Indirect formula by referring to the range of values in the data sheet, I've also tried another variation of the Indirect formula by naming my range - both formulas do not return results, though I know that values exist at my intersection point. If I hard code my Index Array, the formula works and I get the results to populate on my worksheet.

Here are my formulas now:

=IFERROR(INDEX(INDIRECT(_2015_December_Act), MATCH(AB$15&$G17, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "") 

Where _2015_December_Act is a named range on my Essbase data tab for values in the range of Q8:Q356.

and also:

=IFERROR(INDEX(INDIRECT(""&$A$8&"!"&$B$8), MATCH(AB$15&$G16, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")

Where A8 = Essbase Data, and b8 = Q8:Q356 - this range represent the values in the month of December on my Essbase Data sheet.

What am I doing wrong?

I am trying to automate the update of the Array reference, so that the report could be refreshed monthly with minimal manual updates on the Index Array.

  • What are the values stored in Essbase!Q8:Q356? Are they cell references or values? – B540Glenn Feb 1 '16 at 20:28
  • They are values. The essbase tab is essentially a live linked data sheet that will get refreshed with new values from our ERP as we close out each month. – Andra M Feb 2 '16 at 14:10
  • Indirect is expecting a string that will evaluate to a location on a worksheet. Try naming the range ""&$A$8&"!"&$B$8 and using that in place of the name _2015_December_Act. – B540Glenn Feb 2 '16 at 15:25
  • I have actually tried that already, I have 2 versions of the formula posted above, where I tried the named range, then also ""&$A$8&"!"&$B$8. In my A8 and B8 values i have the following values stored: A8 = Essbase Data, and B8 = Q8:Q356. Still not working... – Andra M Feb 2 '16 at 15:29
2

Essbase Data

| Field Name | Jan - 2016 | Feb - 2016 | Mar - 2016 |
| Key 1      | A          | B          | C          |
| Key 2      | D          | E          | F          |
| Key 3      | G          | H          | I          |
| Key 4      | J          | K          | L          |
| Key 5      | M          | N          | O          |

Report

| Date:        | Mar - 2016   |
| Tab:         | Essbase Data |
| Date range:  | ?            |
| Field range: | ?            |
| Data range:  | ?            |
| Column:      | ?            |
|              |              |
| Field Name   | Value        |
| Key 1        | ?            |
| Key 2        | ?            |
| Key 3        | ?            |
| Key 4        | ?            |
| Key 5        | ?            |
| ...          | ...          |

Identifying your ranges

*Remote tab names that contain spaces should be wrapped in single quotes ('Tab name'). Could be a bad font, but from my perspective it looks like they are missing from your formulas above.

Firstly, you want to identify the range for matching your fixed points in time. Date range:

="'" & B2 & "'!$1:$1"

Example output would be 'Essbase Data'!$1:$1. This will be used to MATCH() the desired column.

Secondly, you want to identify the range for matching your field names. Field range:

="'" & B2 & "'!$A:$A"

Example output would be 'Essbase Data'!$A:$A. This will be used to MATCH() the desired row.

Lastly, you want to identify the range of your entire data set. Data range:

="'" & B2 & "'!$A:$D"

Example output would be 'Essbase Data'!$A:$D. This will be used to INDEX() the desired column and row. You could use COUNTA() and ADDRESS() to construct these dynamically. i.e.

="'" & B2 & "'!$A$1:" & ADDRESS(COUNTA('Essbase Data'!A:A), COUNTA('Essbase Data'!1:1))

Example output would be 'Essbase Data'!$A$1:$D$6

Indexing

Firstly, you would want to identify your specified column -using the date range in your example. Column:

=MATCH(B1,INDIRECT(B3),0)

Example output would be 4. Where B1 is the specified heading, and B3 is the specified date range from the Report table above.

Lastly, you would want to create your formula for row indexing that returns it's respective value from the column identified above.

=INDEX(
   INDIRECT($B$5), <-- Data range
   MATCH($A9, <-- Field lookup value
     INDIRECT($B$4), <-- Field range
     0
   ),
   $B$6 <-- Column number
 )

Drag down and your output should look like:

| Field Name   | Value        |
| Key 1        | C            |
| Key 2        | F            |
| Key 3        | I            |
| Key 4        | L            |
| Key 5        | O            |
| ...          | ...          |
  • i was able to follow your instructions up until indexing....I think my challenge is that the Date Range is not straight forward as in your example "Mar - 2106", the data values in my table have the Month and Year in a separate row (no option for the data feed to concatenate the values) which results in March showing up multiple times in one row (I have 2015 and 2016 data in my table). Would you be open to a screen share session to review the workbook construction together? – Andra M Mar 4 '16 at 17:44
  • I wouldn't be opposed, however I work for a financial institution and am under rigid inbound/outbound network traffic rules -would be a little tricky to do at the office. If you could, update your question to reflect the data structure of Esbase Data. I can modify my answer if I had a better understanding of how the data is structured. – jtrumbull Mar 4 '16 at 18:13
  • I just made a couple more tweaks to my formula and was finally able to get it working! Thanks for your help :) – Andra M Mar 4 '16 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.