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

Did you look at the spreadsheet?

LogFile has various Fake columns -- those will all be Hotels and I will have sheets named to match them. I updated the sheet so you can see this more clearly.

So I need a formula that not only pulls in a number based on the Day of Week but also the corresponding hotel on LogFile. The formula could be based on the sheet name itself OR on A2 within the Hotels sheet as it will be the same name, you can look under HOTEL to see A2 says HOTEL.

This formula goes to HOTEL!A40:A300, so you can see how it's currently written.

Does that help?

Yes, I checked the sheet and there is only one hotel and my formula, but I do not want to see my formula, I already know it, what I want to know is what results you want when there is more than one hotel.

 

[Glasgowsmile]

Re: Index Match Help

Yes, I checked the sheet and there is only one hotel and my formula, but I do not want to see my formula, I already know it, what I want to know is what results you want when there is more than one hotel.

Let me try again...

Currently the formula we're using ONLY pulls data for Column D, which means every time I add a new hotel column I will have to change the forumla from D to E or whatever column that specific page needs. That's fine if I'm the only one using this sheet but other people wont be as good with Excel thus I need it to pull the data for them when they add a new column.

What I need is the formula to cover a range from - let's say B1:G300 on the LogFile sheet so when they add or change the name of a hotel it pulls the right data. The formula needs match either the Sheet Name with the Hotel Name on the Logfile and then look for the Day of Week, and then finally pull the numbers for it. That way I don't need to constantly update it from Column D to Column E for every single hotel that gets added.

Does that make sense? If not, I'm at a loss as to how to better explain this.

If you look at the sheet -- you'll see Hotel A - D sheet names and Hotel A - D on the LogFile, I want the formula to look at the sheet name Hotel B and match it across the data in Logfile to find the Hotel B column, then the day of week and pull the data.

I want it this way because eventually the FAKE columns will be Hotel E etc etc and the sheet names will match but I don't want to have to update the formula every single time.

 

[DanteAmor]

Re: Index Match Help

Let me try again...

Currently the formula we're using ONLY pulls data for Column D, which means every time I add a new hotel column I will have to change the forumla from D to E or whatever column that specific page needs. That's fine if I'm the only one using this sheet but other people wont be as good with Excel thus I need it to pull the data for them when they add a new column.

What I need is the formula to cover a range from - let's say B1:G300 on the LogFile sheet so when they add or change the name of a hotel it pulls the right data. The formula needs match either the Sheet Name with the Hotel Name on the Logfile and then look for the Day of Week, and then finally pull the numbers for it. That way I don't need to constantly update it from Column D to Column E for every single hotel that gets added.

Does that make sense? If not, I'm at a loss as to how to better explain this.

If you look at the sheet -- you'll see Hotel A - D sheet names and Hotel A - D on the LogFile, I want the formula to look at the sheet name Hotel B and match it across the data in Logfile to find the Hotel B column, then the day of week and pull the data.

I want it this way because eventually the FAKE columns will be Hotel E etc etc and the sheet names will match but I don't want to have to update the formula every single time.

On the sheet "Hotel" in cell A2 says "Hotel", will that cell always match the name of the sheet?

 

[Glasgowsmile]

Re: Index Match Help

On the sheet "Hotel" in cell A2 says "Hotel", will that cell always match the name of the sheet?

Correct, A2 on each sheet will always match the sheet name as well. The hotel name in LogFile will also always match A2 within the hotel sheet as well as the hotel sheet name.

 

[DanteAmor]

Re: Index Match Help

Correct, A2 on each sheet will always match the sheet name as well. The hotel name in LogFile will also always match A2 within the hotel sheet as well as the hotel sheet name.

Try this array formula

=SI.ERROR(INDICE(LogFile!$C$1:$O$300,SUMAPRODUCTO(K.ESIMO.MENOR(SI(LogFile!$A$2:$A$300=A$8,FILA(LogFile!$A$2:$A$300),""),FILAS(A$2:A2))),COINCIDIR($A$2,LogFile!$C$1:$O$1,0)),"")

 

[Glasgowsmile]

Re: Index Match Help

Try this array formula

=SI.ERROR(INDICE(LogFile!$C$1:$O$300,SUMAPRODUCTO(K.ESIMO.MENOR(SI(LogFile!$A$2:$A$300=A$8,FILA(LogFile!$A$2:$A$300),""),FILAS(A$2:A2))),COINCIDIR($A$2,LogFile!$C$1:$O$1,0)),"")

This looks like it's in spanish - does this work in the English Excel version?

 

[DanteAmor]

Re: Index Match Help

This looks like it's in spanish - does this work in the English Excel version?

I'm sorry, I got excited and forgot to translate.

{=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)),"")}

 

[Glasgowsmile]

Re: Index Match Help

I'm sorry, I got excited and forgot to translate.

{=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)),"")}

Amazing. You're the best - I learned so much from this. Thanks!

 

[DanteAmor]

Re: Index Match Help

Amazing. You're the best - I learned so much from this. Thanks!

In the end I understood your requirement. It was a pleasure to help you, thanks for the feedback.

 

[Glasgowsmile]

Re: Index Match Help

In the end I understood your requirement. It was a pleasure to help you, thanks for the feedback.

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.