Shorten Link within XlookUp

[Yugo101]

Hi All,

Basically my formula is this Xlookup(A2,'Http:///example'!A$2:A$5000,'Http:///example'!b$2:b$5000)

A2 is on the main spreadsheet
The hyper link is another workbook. so basically retrieving data in relation to A2 referenced from another workbook.

Is there way way I can shorten that URL within forumla?. At the moment my formulas are quite long so looking shorten them.

many thanks,

 

[Alex Blakenburg]

I don't know of any way you can shorten the URL but it may help to use the LET statement eg.

=LET(extRng,'Http:///[example]example'!A$2:B$5000,
     XLOOKUP(A2,INDEX(extRng,,1),INDEX(extRng,,2)))

 

[Peter_SSs]

Given the particular example you could also use VLOOKUP instead of XLOOKUP and then you would only need the link path once

VLOOKUP(A2,'Http:///example'!A$2:B$5000,2,0)

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)