Forum Discussion
Force Excel to search from bottom up (not top down)
Do you have Microsoft 365 or Office 2021? If so, use
=IFERROR(HYPERLINK("#"&CELL("address",INDEX(tblPC[[#All],[Invoice amount]],XMATCH(E3*-1,tblPC[[#All],[Invoice amount]],0,-1))),"Take me there"),"Not Found")
Do you have Microsoft 365 or Office 2021? If so, use
=IFERROR(HYPERLINK("#"&CELL("address",INDEX(tblPC[[#All],[Invoice amount]],XMATCH(E3*-1,tblPC[[#All],[Invoice amount]],0,-1))),"Take me there"),"Not Found")
Hi Hans, I have a follow up question if you dont mind.
When I change Match to XMATCH in my production file it still seems to search top down. Not sure if the problem could be that in my production file, the data is not in the same file like it is in the file I posted but other than that I dont know why it searches top down in my production file. I confirm the search is bottom up in this smaller test file I posted even if I put the data in a completely separate file. I am not sure if the number of records has something to do with it since my production file is 10k records.Is there an alternative to XMATCH or an additional tweak to force the search bottom up? thank you very much.
- I see you also added a -1 for the search mode. I didnt see that part. I added it and now it searches bottom up. thank you!
Wow. that worked perfectly. I didnt know that about XMATCH. Nice. thank you!
