Index Match/X-Lookup

[acool]

Hi Everyone,

I am currently trying to create a formula that uses an Index Match/X-Lookup to match values to the corresponding scenario. In this example, I have created a data validation list Cell C16. Depending, on the scenario chose in Cell C16, I would like the values in Cells C18-N18 to update based on the corresponding months. So for example, if scenario 1 is chosen I would like January to be 7.43%, February to be 7.27% etc. The values should line up to the corresponding scenario in Cell C16 as well as the corresponding months. Any help would be greatly appreciated. Thank You!

 

[Fluff]

How about in C18 only

=XLOOKUP(C16,A3:A11,C3:N11)

 

[acool]

How about in C18 only

=XLOOKUP(C16,A3:A11,C3:N11)

@Fluff Thank You for your response. What if the date changes in rows C19:N19? Is there any way to also incorporate matching both the scenario and the dates in row C19:N19? Really appreciate the help here!

 

[Fluff]

Are both row 1 & row 19 actual dates, or just text?

 

[acool]

Are both row 1 & row 19 actual dates, or just text?

Row 1 is text. Row 19 are actual dates

 

[Fluff]

In that case in what way are the dates in row 19 likely to change?

 

[acool]

I have lambda formula that updates those values based on my starting month. In this example, the starting month is January-24 and the formula updates the following 11 months. If I toggle my start date to march as an example, the first value in cell C19 will be March-24. However, I want to make sure that the seasonality scenario always lines up with the month in the first table.

 

[Fluff]

If C19 is Mar 24 would N19 be Feb 25?
If so should M18 & N18 be blank, or should they return the values from C3 & D3

 

[acool]

I would like them to return the values from C3 and D3. As long as the month and scenario matches, that is the value that I would like to have populate. So if March 24 was the starting date, N18 would be February 25 and the value would be 7.27%

 

[Fluff]

Ok, how about

=CHOOSECOLS(FILTER(C3:N11,A3:A11=C16),XMATCH(TEXT(C19:N19,"mmmm"),C1:N1))