vlookup with cell reference for range

users
#1

using libreoffice Version 4.0:build-305 on my opensuse 12.3

I would like to have a reference in the VLOOKUP function for range:
the VLOOKUP formula is:
=VLOOKUP(criteria,range,index,sort)
if I write the function:
=VLOOKUP(1,$A$1:$D$5,2,0)
function works
but
if J1 is $A$1:$D$5
and the formula:
=VLOOKUP(1,J1,2,0)
the formula doesn't works
also if J1 is =ADDRESS(1,1, , )&":"&ADDRESS(5,3, , )

how can I get vlookup function working with cell reference for range??

manythanx, :slight_smile: ciao, pier

#2

Hi,

the VLOOKUP formula is:
=VLOOKUP(criteria,range,index,sort)
if I write the function:
=VLOOKUP(1,$A$1:$D$5,2,0)
function works
but
if J1 is $A$1:$D$5
and the formula:
=VLOOKUP(1,J1,2,0)
the formula doesn't works

Try the function INDIRECT

https://help.libreoffice.org/Calc/Spreadsheet_Functions#INDIRECT

Cheers,
Stefan

#3

"yahoo-pier_andreit":

but
if J1 is $A$1:$D$5
and the formula:
=VLOOKUP(1,J1,2,0)
the formula doesn't works

You probably want INDIRECT function to create a reference from string.

#4

you are right, =VLOOKUP(1,INDIRECT(G1),2,0) works...:slight_smile: :slight_smile: :slight_smile: :slight_smile:

#5

What I have done that works is first name the region. Then I place the region name in a cell, then us indirect(cell with range name). hth

gs