LibreOffice version; os: v7.2.2.2 (x64); windows 10.

If I have a named range, like say ‘myrange’ defined as $sheet2.$a$1:$b$10, I can look up numbers in the range using the vlookup function; for example: vlookup(‘somename’,myrange,2,0).

If I have two named ranges: myrange1 is $sheet2.$a$1:$b$10; myrange2 is $sheet2.$a$11:$b$20, I just need to specify the named range I want in vlookup to use the right table: vlookup(‘somename’,myrange1,2,0), or vlookup(‘somename’,myrange2,2,0).

If I’m not sure which table I want, I want to put the named ranges into a column of data, so column A on sheet1 contains: A1=myrange1; A2=myrange2; A3=myrange1; etc. If I use vlookup(‘somename’,A1,2,0), I expect it will find ‘somename’ in myrange1, and return the value in second column of the named range. This does not work. I just get Err:502.

Is it possible to put the name of a named range into a cell and use it for the lookup range in vlookup()? Am I missing something that would allow this to work? Is this just the wrong way to approach this problem?