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

 

[DanteAmor]

Re: Index Match Help

If it's not too much trouble, could you explain how the formula works? I just want to have a better understanding of what's happening.

{=IFERROR(INDEX(LogFile!$C$1:$O$300,SUMPRODUCT(SMALL(IF(LogFile!$A$2:$A$300=A$8,ROW(LogFile!$A$2:$A$300),""),ROWS(A$2:A2))),MATCH($A$2,LogFile!$C$1:$O$1,0)),"")}


Sumproduct, also used to "search" values.
IF(LogFile!$A$2:$A$300=A$8
Take all the row numbers where A2: A300 are equal to A8
ROW(LogFile!$A$2:$A$300)


SMAL
Of all the rows found, you will take the first lower row, to determine the first lower row, use the formula ROWS(A$2:A2), this formula returns 1.
When you copy the formula down, the formula changes to
ROWS(A$2:A3), the result is 2.
And that means he's going to take the second lower row.


IFERROR(INDEX(LogFile!$C$1:$O$300, row, column)
With the above we have the row.


To get the column
MATCH($A$2,LogFile!$C$1:$O$1
Find the column from C1 to O1 that is equal to cell $A$2


IFERROR, in case of error returns ""