Index Match Return Second Column, not first

CPR83

New Member
Hello,

I am building a template based on a auto-generated template coming out of our financial system. Right now the source data is
July August September
AreaActualBudgetActualBudgetActualBudget
Area1101512151218
Area2121812181318
Area3598946
Area4265849

<tbody>
</tbody>


My end goal is to create an index match to return a table that looks like this:

Actuals
AreaJulyAugustSeptemberOctoberNovember
Area1101212
Area2121213
Area3584
Area4254

<tbody>
</tbody>


Right now I have it set up to index match successfully on the first column for Actuals. I want to create another table for just the budget. I know I can do this with a simple link but I like having these checks in place. Right now my index match checks against a cell with "Actuals" against the column heading of the source table.
This is my current formula and it works well for the Actuals table:
=INDEX('Total'!$C$7:$AN$22,MATCH(Sheet1!$B8,'Total'!$A$7:$A$22,0),MATCH(Sheet1!E$4,'Total'!$C$4:$AN$4,0),MATCH($B$3,''!$C$5:$AN$5,0))

Only problem is I can't figure out how to return the budget column since the formula above is returning the first column automatically.

I've been searching for an answer but can't seem to come up with a solution.
 

Scott T

Well-known Member
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">July</td><td style=";">July</td><td style=";">August</td><td style=";">August</td><td style=";">September</td><td style=";">September</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Area</td><td style=";">Actual</td><td style=";">Budget</td><td style=";">Actual</td><td style=";">Budget</td><td style=";">Actual</td><td style=";">Budget</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Area1</td><td style="text-align: right;;">10</td><td style="text-align: right;;">15</td><td style="text-align: right;;">12</td><td style="text-align: right;;">15</td><td style="text-align: right;;">12</td><td style="text-align: right;;">18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Area2</td><td style="text-align: right;;">12</td><td style="text-align: right;;">18</td><td style="text-align: right;;">12</td><td style="text-align: right;;">18</td><td style="text-align: right;;">13</td><td style="text-align: right;;">18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Area3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">9</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Area4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">8</td><td style="text-align: right;;">4</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Actual</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Area</td><td style=";">July</td><td style=";">August</td><td style=";">September</td><td style=";">October</td><td style=";">November</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Area1</td><td style="text-align: right;;">10</td><td style="text-align: right;;">12</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Area2</td><td style="text-align: right;;">12</td><td style="text-align: right;;">12</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Area3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">8</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Area4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">budget</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Area</td><td style=";">July</td><td style=";">August</td><td style=";">September</td><td style=";">October</td><td style=";">November</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Area1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Area2</td><td style="text-align: right;;">18</td><td style="text-align: right;;">18</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Area3</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">Area4</td><td style="text-align: right;;">6</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$4:$G$7,MATCH(<font color="Red">$A12,$A$4:$A$7,0</font>),MATCH(<font color="Red">B$11&$A$10,$B$2:$G$2&$B$3:$G$3,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C12</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$4:$G$7,MATCH(<font color="Red">$A12,$A$4:$A$7,0</font>),MATCH(<font color="Red">C$11&$A$10,$B$2:$G$2&$B$3:$G$3,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D12</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$4:$G$7,MATCH(<font color="Red">$A12,$A$4:$A$7,0</font>),MATCH(<font color="Red">D$11&$A$10,$B$2:$G$2&$B$3:$G$3,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B20</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$4:$G$7,MATCH(<font color="Red">$A20,$A$4:$A$7,0</font>),MATCH(<font color="Red">B$11&$A$18,$B$2:$G$2&$B$3:$G$3,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C20</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$4:$G$7,MATCH(<font color="Red">$A20,$A$4:$A$7,0</font>),MATCH(<font color="Red">C$11&$A$18,$B$2:$G$2&$B$3:$G$3,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D20</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$4:$G$7,MATCH(<font color="Red">$A20,$A$4:$A$7,0</font>),MATCH(<font color="Red">D$11&$A$18,$B$2:$G$2&$B$3:$G$3,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

Swayzy

Board Regular
Personally I like using sumifs and dates Excel understands. By using sumifs then it is much easier to use a criteria for summing.

So in your case I would replace the dates if they arent in the real Excel way and them. Then build your separate tables that also have dates on top. You can format them as displaying the month name not an actual date.

Set the dates with the eomonth function. For the File that comes from the system you will need do a double month setup so there are dates for both bud and act. Just set two cells to end of july then to the right use two cells with eomonth functions and ref to those two cells. Then you mark both cells with the eomonth functions and drag to the right
 
Last edited:

Fluff

MrExcel MVP, Moderator
Maybe
INDEX('Total'!$C$7:$AN$22,MATCH(Sheet1!$B8,'Total'!$A$7:$A$22,0),MATCH(Sheet1!E$4,'Total'!$C$4:$AN$4,0)+1)
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top