I need to return a value corresponding to date

[KalyanPrasad]

Hello, 

my input data says this. Before 01-August, the administration of City 1 was overseen & supported by State 1

After 10th-August, City 1 was supported by State2, and from date 20th-Aug onwards, it is being supported by State3. 
Like this i have many cities and corresponding states changing based on date values

How do i return the corresponding state value for a city, for that date condition ? 

i m tried this in State Column. But not working.
=XLOOKUP([@City],DATEVALUE(Table1[[#Headers],[01-Aug-24]:[20-Aug-24]])>=[@Date],Table1[[01-Aug-24]:[20-Aug-24]],"",0,1)
Need suggestions for the match logic.

Thank you
kalyan


Thank you. 

[#2]

@KalyanPrasad 

Try

=INDEX(Table1[[2024-08-01]:[2024-08-20]], XMATCH([@City], Table1[City]), XMATCH([@Date], DATEVALUE(Table1[[#Headers],[2024-08-01]:[2024-08-20]]), -1))

[#3]

not working. thanks for trying.

[#4]

@KalyanPrasad 

You have to use the names of the columns in your table:

=INDEX(Table1[[01-Aug-24]:[20-Aug-24]], XMATCH([@City], Table1[City]), XMATCH([@Date], DATEVALUE(Table1[[#Headers],[01-Aug-24]:[20-Aug-24]]), -1))

[#5]

=INDEX(Table1[[01-Aug-24]:[20-Aug-24]], XMATCH([@City], Table1[City]), XMATCH([@Date], DATEVALUE(Table1[[#Headers],[01-Aug-24]:[20-Aug-24]]), -1))

[#6 · Accepted solution]

@KalyanPrasad 

As variant

=INDEX( XLOOKUP([@Date], 1*DROP(Source[#Headers],,1),DROP(Source,,1),,-1),
        XMATCH([@City],Source[City] ) )

[#7]

Works like Magic. Thank you.

[#8]

@KalyanPrasad , you are welcome