Referencing named ranges

stderr · November 24, 2021, 7:44pm

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?

EarnestAl · November 24, 2021, 8:20pm

Use VLOOKUP(‘somename’,INDIRECT(A1),2,0)

stderr · November 24, 2021, 8:27pm

You da man! Thanks!!!

karolus · November 24, 2021, 9:29pm

why the hell did you not simply define another named range with reference to
$sheet2.$a$1:$b$20

stderr · November 24, 2021, 11:24pm

It’s and example to illustrate the problem I’m trying to solve.

You asked why. Using plywood as an example, if I have two different grades, A & B, and each comes in six different thicknesses, I want one table to have prices of grade A by thickness, the other to have prices of grade B by thickness. If doing a cost calculation, I specify grade B using the name of the named range of the lookup table, and in the next column give the thickness, I’d like to do a vlookup on the grade B table mapping thickness to price; both tables have the same thickness in their leftmost column, but different prices.

If I just include the grade in the named range, i.e. make it one all-encompassing table with 12 entries, there would be two entries for each thickness, and vlookup stops on the first match it gets. If I make the leftmost column grade, vlookup will return on the first hit, say 1/4". If I make the reference column the thickness instead, there will be two entries per thickness, and again, vlookup returns the first hit.

This is a grossly oversimplified example to explain the problem. If this is not the smart way of doing this, school me.