Refine Formula for Querying Two Tables Using Index/Match

[rkaczano]

I am looking to see if I can replace the following formula below with something more elegant. It works fine but I want to see if I can streamline it.

I have two identically dimensioned tables (each 7*26) ) that I am querying. The tables represent hours in a day in first column (1-24) , and days of week with two top header rows with notation on whether this is a weekday or weekend. The two tables represent different seasons (Winter and Summer). And I have populated each table with unique values as follows:

Winter Table: $O$14:$U$37
Summer Table: $D$14:$J$37

In a third table I have a column and 8760 rows (hours in a year). Each row represents an hour in the year. In each row I am testing to see which hour of which day of which season I am in so that I can pull seasonal hourly data that I have in the above mentioned Winter/Summer Tables. I only use these two tables of data for the entire year.

To perform the query I use an Index/Match/Match to find the hour and day and then use an IF stmt pick the correct table. In cell AZ5 I have the following formula which I copy down. It tests which hour I am in. It also checks to see if the current hour falls within a holiday or not. If so, it defaults to a "Saturday" otherwise is queries the actual day of the week in the table. In cell AZ5 I have the following formula which I copy down. It tests which hour I am in. It also checks to see if the current hour falls within a holiday or not. If so, it defaults to a "Saturday". In cell AZ5 I have the following formula which I copy down. It tests which hour I am in. It also checks to see if the current hour falls within a holiday or not. If so, it defaults to a "Saturday".

Annual Hourly Table:
AG5: Current Day of Week
AK5: Current Hour
AX5: Current Season
AW5: Current Day (Regular or Holiday)
AZ5: See formula below

=IF(AX5="Winter", INDEX($O$14:$U$37,MATCH(AK5/100,$N$14:$N$37,0),MATCH(IF(AW5="Holiday", "Saturday",AG5),$O$12:$U$12,0)),

Otherwise (Summer):

INDEX($D$14:$J$37,MATCH(AK5/100,$N$14:$N$37,0),MATCH(IF(AW5="Holiday", "Saturday",AG5),$O$12:$U$12,0)))

One way to clean it up would be to use XLookup instead of Index(Match). But I am wondering if there is a way to structure this so that I do not have to repeat the second INDEX/Match/Match if the Seasonal Condition does not hold.

Any ideas?

Thanks

 

[Peter_SSs]

Sounds like you have MS365 but in any case I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try this

=INDEX(IF(AX5="Winter",$O$14:$U$37,$D$14:$J$37),MATCH(AK5/100,$N$14:$N$37,0),MATCH(IF(AW5="Holiday", "Saturday",AG5),$O$12:$U$12,0))