Make Formula Dynamic

[SanjayGMusafir]

Hi Experts
Underneath is my workaround formula, which looks for the Current Row Value [@PMobile] in/until the rows above (Column AR - Header[PMobile] ) in a structured table and returns value from the Column [sP] (Column AO)

=LET(St,XLOOKUP([@PMobile],$AR$2:AR2,$AO$2:AO2,"",0),IFS(St=0,"",TRUE,St))

The above is working fine.

I initially tried to make it dynamic as below, but I understand that I made some strange blunder. Can you help me make it dynamic.

=LET(St,XLOOKUP([@PMobile],Clients[[#Headers],[PMobile]]:OFFSET([@PMobile],-1,0),Clients[[#Headers],[sP]]:OFFSET([@sP],-1,0),"",0),IFS(St=0,"",TRUE,St))

Unable to copy XL2BB to give the reference - It is giving some error

Thanks in Advance
Highly Appreciated

 

[RoryA]

I don't understand what you are trying to achieve - your formula is already 'dynamic' in any sense I can think of.

 

[SanjayGMusafir]

'dynamic' in any sense I can think of

Thanks @RoryA for reverting

I was looking for an option to get solution using table structure references, if you may help

Thanks again

 

[RoryA]

Your OFFSET formula works for me. What is happening when you use it?

 

[SanjayGMusafir]

Your OFFSET formula works for me. What is happening when you use it?

Let me recheck again, I was in middle of so many things that I might have missed something.

Thanks again

 

[SanjayGMusafir]

Your OFFSET formula works for me. What is happening when you use it?

Hi @RoryA
Thanks for reverting

I was in middle of so many things, so didn't get chance to test things out.

You are right. The formula is working. Probably I overlooked something when I was testing it.

Sorry for the inconvenience
And Thanks again for reverting.