Index Match

[Glasgowsmile]

Hello!

Running into an issue -- I think I can resolve it with Index, Match but I'm not entirely sure.

Let's say, I've got weekly data coming in Sun-Sat each week, all year long.

I want to find the Average of the Data on any given Weekday.

I want it to look at the DoW (let's say Sunday), go to the right worksheet and find all the of Sundays and then the data to the right for each date, then bring it back to the previous worksheet so I can average it out.

Photos below to help clarify. Please let me know if you need any further information.

https://imgur.com/a/u56ohbA

https://imgur.com/a/WHCXNmg

 

[Glasgowsmile]

Re: Index Match Help

Currently this is the code I'm trying, and I've been using CSE with it as an array formula but it doesn't seem to matter. I'm missing something here.

I need it to match Sunday (A20) with the Sunday's in B31:B46 and pull the data from C31:C45

=INDEX(C31:C45,MATCH(A20,B31:B46,0),1)

 

[DanteAmor]

Re: Index Match Help

There is no data in your images.
You could upload a file or images with data and explain with these data what you need and what is the desired result.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Glasgowsmile]

Re: Index Match Help

I'll try again here:

https://imgur.com/a/F5bgD09 -- this is where the data will go

https://imgur.com/a/MITP1VH -- this is the data I'm pulling from, I have Sunday listed in Column A as text and Column B as the date, the format will be changed in Column B later so I found out I needed column A as text DoW or the index match wouldn't work at all.

This index/match works except it's only pulling the first number which is 16 for the first Sunday, I need it to pull in all the numbers (not a sum, but a list) for each Sunday that has data to put below where it says "Sunday" on Image one, so I can then run an average formula for the rest of that data.

=INDEX(LogFile!C2:O19,MATCH(HOTEL!A20,LogFile!A2:A16,0),2)

 

[DanteAmor]

Re: Index Match Help

Try this array formula

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>LogFile</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:103.6px;" /><col style="width:42.77px;" /><col style="width:42.77px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DAY</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">B</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">C</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Hotel</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Saturday</td><td > </td><td > </td><td style="text-align:right; ">16</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Sunday</td><td > </td><td > </td><td style="text-align:right; ">16</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Monday</td><td > </td><td > </td><td style="text-align:right; ">8</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Tuesday</td><td > </td><td > </td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Wednesday</td><td > </td><td > </td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Thursday</td><td > </td><td > </td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Friday</td><td > </td><td > </td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Saturday</td><td > </td><td > </td><td style="text-align:right; ">6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Sunday</td><td > </td><td > </td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Monday</td><td > </td><td > </td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Tuesday</td><td > </td><td > </td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Wednesday</td><td > </td><td > </td><td style="text-align:right; ">9</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Thursday</td><td > </td><td > </td><td style="text-align:right; ">11</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Friday</td><td > </td><td > </td><td style="text-align:right; ">13</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Saturday</td><td > </td><td > </td><td style="text-align:right; ">15</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >Sunday</td><td > </td><td > </td><td style="text-align:right; ">17</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >Monday</td><td > </td><td > </td><td style="text-align:right; ">19</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >Tuesday</td><td > </td><td > </td><td style="text-align:right; ">21</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >Wednesday</td><td > </td><td > </td><td style="text-align:right; ">23</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >Thursday</td><td > </td><td > </td><td style="text-align:right; ">25</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >Friday</td><td > </td><td > </td><td style="text-align:right; ">27</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >Saturday</td><td > </td><td > </td><td style="text-align:right; ">29</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >Sunday</td><td > </td><td > </td><td style="text-align:right; ">31</td></tr></table>

---
<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Hotel</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:88.4px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Avg</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Avg</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Avg</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Avg</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Avg</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Avg</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Avg</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Sunday</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Monday</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Tuesday</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Wednesday</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Thursday</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Friday</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Saturday</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">16</td><td style="text-align:right; ">8</td><td style="text-align:right; ">5</td><td style="text-align:right; ">4</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">16</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">2</td><td style="text-align:right; ">5</td><td style="text-align:right; ">7</td><td style="text-align:right; ">9</td><td style="text-align:right; ">11</td><td style="text-align:right; ">13</td><td style="text-align:right; ">6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">17</td><td style="text-align:right; ">19</td><td style="text-align:right; ">21</td><td style="text-align:right; ">23</td><td style="text-align:right; ">25</td><td style="text-align:right; ">27</td><td style="text-align:right; ">15</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">31</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">29</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >A21</td><td >{=IFERROR(INDEX(LogFile!$D$1:$D$24,SUMPRODUCT(SMALL(IF(LogFile!$A$2:$A$24=A$20,ROW($A$2:$A$24)),ROWS(A$2:A2)))),"")}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Copy down then copy right.

 

[Glasgowsmile]

Re: Index Match Help

Thanks -- everything seems to be working great!

One Minor issue -- some of these cells are pull zeros for blank cells, I added the ,"" to the If statement if false but it didn't work. How do I remove those 0's?

 

[DanteAmor]

Re: Index Match Help

Thanks -- everything seems to be working great!

One Minor issue -- some of these cells are pull zeros for blank cells, I added the ,"" to the If statement if false but it didn't work. How do I remove those 0's?

[FONT=&quot]In the Format Cells dialog box, click Number button from the tab, and then click Custom from the Category list. In the Type box, type 0;-0;;@. See screenshot:[/FONT]
[FONT=&quot]

[/FONT]

 

[Glasgowsmile]

Re: Index Match Help

Does this work on All 0's or just blank cells? I want the 0's if the data is actually 0 but if it's blank, I don't want it to show up as 0 like it had before but instead remain blank.

 

[Glasgowsmile]

Re: Index Match Help

One other thing, for future issues.

Currently it's just for Hotel -- what if I add another hotel in the future or I've got

Hotel | Hotel 1 | Hotel 2 | Hotel 3

Can I have one formula to match the hotel name to the right column and pull the number?

 

[DanteAmor]

Re: Index Match Help

Does this work on All 0's or just blank cells? I want the 0's if the data is actually 0 but if it's blank, I don't want it to show up as 0 like it had before but instead remain blank.

{=IFERROR(IF(INDEX(LogFile!$D$1:$D$24,SUMPRODUCT(SMALL(IF(LogFile!$A$2:$A$24=A$20,ROW($A$2:$A$24)),ROWS(A$2:A2))))="","",INDEX(LogFile!$D$1:$D$24,SUMPRODUCT(SMALL(IF(LogFile!$A$2:$A$24=A$20,ROW($A$2:$A$24)),ROWS(A$2:A2))))),"")}