Excel Is it possible to reorder the data in 2 columns to match up if they have a certain number of characters a string in common

Question

I'm now rearranging a sizable number of media files in an effort to make them adhere to a tracking system. I currently have two lists, one of which is a list of all the URLs from which the videos have been ripped.

The names of the.mp4 files from the bulk download are the other.

Column A looks like

C:\Users\XXX\BIN\MEDIA\1614561651289915393_2.mp4
C:\Users\XXX\BIN\MEDIA\1615390470523191305_2.mp4
C:\Users\XXX\BIN\MEDIA\1612041791171084288_2.mp4
C:\Users\XXX\BIN\MEDIA\1612425997130911747_2.mp4

Column B looks like:

https://twitter.com/user/status/1611717485828489221?s=12&t=HFVIWyICVNxavFltOixxVg
https://twitter.com/user/status/1612425997130911747?s=12&t=gU4QIpi1peMHASLbiRGKjA
https://twitter.com/user/status/1612831672805855232?s=12&t=IQ_M-PpP05ylmIRsBjlIfg
https://twitter.com/user/status/1604835107759853568?s=12&t=wzY0CTl6lyINeaFxisyZcQ
 

Column A should be rearranged such that entries correspond to the URLs that contain the filename between /status/ and?s=12&t.

Is that plausible or does it make sense?

Answer 1

Try this:

=LET(files,A1:A4,
URLs,B1:B4,
f,BYROW(files,LAMBDA(r,TEXTSPLIT(TAKE(TEXTSPLIT(r,"\"),,-1),"_"))),
XLOOKUP("*/" & f &"~?*",URLs,URLs,"not found",2))