INDEX/MATCH combination or VLOOKUP to overcome max character limit

Hi Everyone

I have a spreadhseet (can use excel 2019 if needed) with 2 tabs, Sheet1 contain "search link" in column A and column B contain "ID Number"
docs.google.com/spreadsheets/d/1iflvQaYTHyxZYjAaIUQE4as3qhPiyt0bjszPEdHyNTk/edit#gid=272903372
Sorry for bypassing limit for new user but I am not allowed to post images either

Now on second tab "Sheet 2" I need to find ID numbers in column B if i give data in column A. This works perfectly fine when I use simple vlookup or Index match function when lookup value is not a "search link"
=INDEX(Sheet1!B:B,MATCH(Sheet2!A2,Sheet1!A:A,0))
OR
=VLOOKUP(A2,Sheet1!A$2:B$42521,2,0)

Please help, Its just a sample and I'll have to work with big database of links and match them with ID numbers
Any help will be appreciated

Second query if first is solved:-

What if I add more data fields in sheet 1 and want to retrieve them in corresponding columns in sheet 2
e.g add "Sales Rep", "Comment","Opportunity?" etc... and want to get those details in sheet 2 by looking up data in column A "Search Link"
=VLOOKUP($A2,Sheet1!$2:$1048576,COLUMNS($B2:B2)+1,0)
I've used this formula and just drag to right side to copy formula and it works perfectly fine but not with search link as input. So I am stuck here as well

Thanks for the reply, I agree that no one should work from scratch so here is the excel sheet that contain formula and link to spreadsheet along with the desired result in row 2.
I would like to work on Google spreadsheet but if there is a problem in getting desired result then I don't mind working on excel

I tried vlookup formula on spreadsheet and it worked but doesn't help with second question

Let me know if that helps you need more info

Amazing "rorya" Exactly what I needed. Thanks a lot

Sorry can't tag you due to forum limitations for new user

I did try to give Rep but don't see anything? Marking this thread as solved