Calc - VLOOKUP - how to use 'database range' as array

EngDarkAlchemist · June 26, 2024, 5:12pm

Hi team
I want to use a ‘database range’ in a VLOOKUP function, this because my array is growing up constantly.
Currently my fucntion is

=TRIM(CONCATENATE(IF(Q41245="","",VLOOKUP(Q41245,**$Relacionamentos.$M$2:$Relacionamentos.$O$907**,3,0))," ",IF(Q41245="","",VLOOKUP(Q41245,$Relacionamentos.$M$2:$Relacionamentos.$O$907,2,0))))

I want to use my ‘database range’ “Relac_Products” instead, so, changing from “$Relacionamentos.$M$2:$Relacionamentos.$O$907” to @ Relac_Produts, or {Relac_Products}…

example: =TRIM(CONCATENATE(IF(Q41245="","",VLOOKUP(Q41245,Relac_Products,3,0))," “,IF(Q41245=”","",VLOOKUP(Q41245,Relac_Products,2,0))))
I tried using range name only, between {}, between [], @, nothing worked.

Relac_Products

Could you please, help me? Thank you in advanced.

Villeroy · June 26, 2024, 6:13pm

If you simply insert new rows, both, the db-range and the reference $Relacionamentos.$M$2:$Relacionamentos.$O$907, expand automatically.
107291.ods (17.2 KB)

EngDarkAlchemist · June 26, 2024, 7:11pm

Thank you so much! I will try your formula as well.

I found that using only the database range name work :
=CONCATENATE(IF(Q9995="","",VLOOKUP(Q9995,Relac_Products,3,0))," ",IF(Q9995="","",VLOOKUP(Q9995,Relac_Products,2,0)))