How to use Variable Lookup_array with Xmatch formula, =XMATCH(Student_Name,$B$3:B216,0,-1)

[dinesh321t]

I am using and excel to make student exam marks record to see progress, keeping latest three records, last record is perfectly shown using xmatch but second last or third last having error, excel consider first loocup_array reference only but i want all reference should be taken from last one using named range

only one formula calculates all

tired =XMATCH(Student_Name,$B$3:B216,0,-1)

and

=XMATCH(Student_Name,Latest_Marks,0,-1) but not works

tired =XMATCH(Student_Name,$B$3:B216,0,-1)

and

=XMATCH(Student_Name,Latest_Marks,0,-1) but not works

[Glenn Kennedy]

One way:

Please Login or Register  to view this content.

[Glenn Kennedy]

This is much neater... I'm more awake...


=LET(A,A3:A219,B,B3:B219,C,UNIQUE(B),HSTACK(C,DROP(REDUCE("",C,LAMBDA(x,y,VSTACK(x,TRANSPOSE(TAKE(SORT(FILTER(A,B=y),,-1),3))))),1)))

[HansDouwe]

Another way:

Please Login or Register  to view this content.

[dinesh321t]

Thanks for quick response, its working downloaded sheet solved by you

but not working when I update any value in existing like student name in or marks

can this be solved with Named Range as I am using another sheet to get data via Named ranged

Thanks for your understanding

[dinesh321t]

Another way:

Please Login or Register  to view this content.

Re: How to use Variable Lookup_array with Xmatch formula, =XMATCH(Student_Name,$B$3:B216,0
Thanks for quick response, its working downloaded sheet solved by you

but not working when I update any value in existing like student name in or marks

all Result update with single #NAME?

can this be solved with Named Range as I am using another sheet to get data via Named ranged

the sheet will be updated everyday so required normal formula

Thanks for your understanding

[HansDouwe]

Which Version of MS365 are you using (that are 4 digits, mostly starts with 22)

[dinesh321t]

Which Version of MS365 are you using (that are 4 digits, mostly starts with 22)

Version 2105 (Build 14026.20334)

[HansDouwe]

Your MS365 is too old, you need version 2203 or newer.

[dinesh321t]

Your MS365 is too old, you need version 2203 or newer.

is any other option which solve this problem using office 2016 or 2019 or 2105

I understand you have given best formula which calculate all things using a single command

is there any option we use different formulas for each column which refer previous result

I understand you can as previous solution given in a while

[HansDouwe]

Using 365 Version 2105, please try in K3:

Please Login or Register  to view this content.

and in L3 and copy down:

Please Login or Register  to view this content.

[dinesh321t]

Using 365 Version 2105, please try in K3:

Please Login or Register  to view this content.

and in L3 and copy down:

Please Login or Register  to view this content.

Thank You so much

It's perfectly working and solve my all query; I know you can solve it as previous one also best but not works with me due to version issue

current solution works on older version also

[HansDouwe]

Thanks for the feedback and glad to have helped.

Please considder adding reputation to the answers of any helpers if you have appreciated the help and feel that the helpers have earned it.
If you want to, click on *Add reputation at the bottom left of the answers.

Happy holidays.

[HansDouwe]

Thanks for the rep .