dynamic max formula

[daveyc18]

=MAX('Statement C'!$AD$32:$AP$32)*1000


most likely it'll stay on row 32, but just in case i'd like this to be dynamic just in case the row number moves


i can get the row number by....=ROW(XLOOKUP(10,'Statement c'!U:U,'Statement c'!U:U))


is there a way to somehow integrate that row number into the max formula?

 

[Fluff]

How about

=MAX(index('Statement C'!$AD:$AP,xmatch(10,'Statement c'!U:U),))*1000

 

[daveyc18]

How about

=MAX(index('Statement C'!$AD:$AP,xmatch(10,'Statement c'!U:U),))*1000

perfect, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[daveyc18]

How about

=MAX(index('Statement C'!$AD:$AP,xmatch(10,'Statement c'!U:U),))*1000

one question , so the "," at the end makes index return the entire array for that row ?

 

[Fluff]

That's right.