Referencing named ranges

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?

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

1 Like

You da man! Thanks!!!

why the hell did you not simply define another named range with reference to

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.