Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Mar 04, 2023
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 ...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Mar 04, 2023

    Tony2021 

    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")

Tony2021's avatar
Tony2021
Iron Contributor
Mar 04, 2023

HansVogelaar 

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.   

 

 

 

Tony2021's avatar
Tony2021
Iron Contributor
Mar 05, 2023
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!

Resources