Forum Discussion
Solved
Force Excel to search from bottom up (not top down)
Hello Experts,
I have attached a file.
I am using a "hyperlink" to search by amount.
If I click on the hyperlinked cell it takes me to the amount
However, it searches top down and I want it to search bottom up.
the formula I am using:
=IFERROR(HYPERLINK("#"&CELL("address",INDEX(tblPC[[#All],[Invoice amount]],MATCH(E3*-1,tblPC[[#All],[Invoice amount]],0))),"Take me there"),"Not Found")
Please see attached sample file.
My production file has 1000's of rows.
thank you
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!
