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

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

Is this for the blank issue or does this formula fix the other question about their being multiple hotels and I need to find the right one on the list and pull the data for the day of week too?

 

[DanteAmor]

Re: Index Match Help

Is this for the blank issue or does this formula fix the other question about their being multiple hotels and I need to find the right one on the list and pull the data for the day of week too?

It is for 0 / blank

 

[Glasgowsmile]

Re: Index Match Help

To clarify something on this:

It could match the sheet name (Hotel 1, Hotel 2, Hotel 3) with the column or it could match with cell Hotel1!A2 or Hotel2!A2 as A2 on the specific sheet will have the hotel name as well.

Would it be helpful if I upload a general copy of the spreadsheet for you to review?

 

[DanteAmor]

Re: Index Match Help

Try this array formula

LogFile

<tbody>
</tbody>

	A	B	C	D
1	DAY	B	C	Hotel
2	Saturday			16
3	Sunday			16
4	Monday			8
5	Tuesday			5
6	Wednesday			4
7	Thursday			3
8	Friday			4
9	Saturday			6
10	Sunday			2
11	Monday			5
12	Tuesday			7
13	Wednesday			9
14	Thursday			11
15	Friday			13
16	Saturday			15
17	Sunday			17
18	Monday			19
19	Tuesday			21
20	Wednesday			23
21	Thursday			25
22	Friday			27
23	Saturday			29
24	Sunday			31

<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><tbody>
</tbody>

---

Hotel

<tbody>
</tbody>

	A	B	C	D	E	F	G
1
19	Avg	Avg	Avg	Avg	Avg	Avg	Avg
20	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
21	16	8	5	4	3	4	16
22	2	5	7	9	11	13	6
23	17	19	21	23	25	27	15
24	31						29
25
26

<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><tbody>
</tbody>

Formeln der Tabelle

Zelle Formel A21 {=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)))),"")} <tbody> </tbody>

<tbody>
</tbody>

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.

This works:

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?

To fix 0 or blank use this:

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

 

[Glasgowsmile]

Re: Index Match Help

I'm confused -- this formula below will work to pull data in the B or C column if I were to add a new hotel in those columns, because currently it's only covering D1:D24 -- will it work if I expand it from B1:D24, doesn't when I tried.

I have other hotels I need to add but I'd prefer to not have to change D1:D24 to fit the right column in each sheet but instead it look at the hotel name and day of week, and pull the correct data.

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

 

[DanteAmor]

Re: Index Match Help

I'm confused -- this formula below will work to pull data in the B or C column if I were to add a new hotel in those columns, because currently it's only covering D1:D24 -- will it work if I expand it from B1:D24, doesn't when I tried.

I have other hotels I need to add but I'd prefer to not have to change D1:D24 to fit the right column in each sheet but instead it look at the hotel name and day of week, and pull the correct data.

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

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


The original formula I sent you is to extract data from Col. D. Check the example I put.
If you want to get data from another column, you have to explain to me again what you want to do.
With examples

 

[Glasgowsmile]

Re: Index Match Help

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


The original formula I sent you is to extract data from Col. D. Check the example I put.
If you want to get data from another column, you have to explain to me again what you want to do.
With examples

No problem - this might be easier, here is a copy of a fake spreadsheet with this data that I've been working on https://www.dropbox.com/s/d1hu3uvzw4aaeox/FakeHotelReview.xlsm?dl=0

The specific formula is going into Hotel!A40:G300, it's currently just pulling column D in LogFile.

The problem is, I have a lot of other columns in the LogFile labeled "Fake" but eventually will be Hotel 1, Hotel 2 etc.

So I'd like the formula to span the entire dataset LogFile!C2:O300 instead of D2:D300. I would like it to Match the hotel name in Hotel!A2 with the hotel name in LogFile, as well as Match the day of week (Sun-Sat), then pull the numbers corresponding with that Day of Week into the Hotel Spreadsheet.

Each hotel will have their own tab and it will be named after the hotel itself so you could just have it match against the tab name instead if that is easier.

 

[Glasgowsmile]

Re: Index Match Help

No problem - this might be easier, here is a copy of a fake spreadsheet with this data that I've been working on https://www.dropbox.com/s/d1hu3uvzw4aaeox/FakeHotelReview.xlsm?dl=0

The specific formula is going into Hotel!A40:G300, it's currently just pulling column D in LogFile.

The problem is, I have a lot of other columns in the LogFile labeled "Fake" but eventually will be Hotel 1, Hotel 2 etc.

So I'd like the formula to span the entire dataset LogFile!C2:O300 instead of D2:D300. I would like it to Match the hotel name in Hotel!A2 with the hotel name in LogFile, as well as Match the day of week (Sun-Sat), then pull the numbers corresponding with that Day of Week into the Hotel Spreadsheet.

Each hotel will have their own tab and it will be named after the hotel itself so you could just have it match against the tab name instead if that is easier.

Did this clarify what I was looking for?

 

[DanteAmor]

Re: Index Match Help

No problem - this might be easier, here is a copy of a fake spreadsheet with this data that I've been working on https://www.dropbox.com/s/d1hu3uvzw4aaeox/FakeHotelReview.xlsm?dl=0

The specific formula is going into Hotel!A40:G300, it's currently just pulling column D in LogFile.

The problem is, I have a lot of other columns in the LogFile labeled "Fake" but eventually will be Hotel 1, Hotel 2 etc.

So I'd like the formula to span the entire dataset LogFile!C2:O300 instead of D2:D300. I would like it to Match the hotel name in Hotel!A2 with the hotel name in LogFile, as well as Match the day of week (Sun-Sat), then pull the numbers corresponding with that Day of Week into the Hotel Spreadsheet.

Each hotel will have their own tab and it will be named after the hotel itself so you could just have it match against the tab name instead if that is easier.

But you did not put any example of what you expect of result, minimum put 5 examples to understand what you need.
In the "LogFile" sheet you only have a "Hotel" column, but you say you will have several, then put several "Hotel" columns and on the "Hotel" sheet you explain what results you want.

 

[Glasgowsmile]

Re: Index Match Help

But you did not put any example of what you expect of result, minimum put 5 examples to understand what you need.
In the "LogFile" sheet you only have a "Hotel" column, but you say you will have several, then put several "Hotel" columns and on the "Hotel" sheet you explain what results you want.

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?