Bug 123746 - Allow VLOOKUP function to work on the left specifying a negative index
Summary: Allow VLOOKUP function to work on the left specifying a negative index
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Reported: 2019-02-27 15:49 UTC by Antonello
Modified: 2019-11-16 06:38 UTC (History)
0 users

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Antonello 2019-02-27 15:49:27 UTC
The VLOOKUP() function works specifying a value to look-up in a column X and the number of columns on the right where to retrieve the desired output on the same raw of the founded value.

Often however I find myself to have the value to look-up on the right, and the return value on the left.

My suggestion is to allow specifying a negative index of number of column and this would have to be interpreted to lookup the value on the rightmost column of the interval given and return the value on the specified column on the left.

Steps to Reproduce:
VLOOKUP(A1:D10,"car",2,0) (current implementation) returns the value in the B column at the row where on column A the word "car" is found. 

VLOOKUP(A1:D10,"car",-2,0) (proposed implementation) should return the value in the C column at the row where on column D the word "car" is found. 

Actual Results:
Negative index results in an error

Expected Results:
Negative index should mirror the request and look at the left of the rightmost column.

Reproducible: Always

User Profile Reset: No

Additional Info:
I never use it, but the same thing could possibly apply also to HLOOKUP()
Comment 1 Eike Rathke 2019-02-27 16:36:02 UTC
Won't be implemented as it is not compatible/interoperable with any other spreadsheet implementation.

If you need such functionality then use MATCH() to obtain the index and feed it to OFFSET(), as in =OFFSET(A2;MATCH("c";B2:B9;0)-1;0)
Comment 2 gmolleda 2019-04-14 08:00:30 UTC
Related bugs: https://bugs.documentfoundation.org/show_bug.cgi?id=124728
In other spreadsheet you can modify the matrix with the functions CHOICE:
	A	B
1	a	1
2	b	2
3	c	3
4	d	4

You can use for search: =VLOOKUP(2;CHOICE({2\1};a1:a4;b1:b4);2;FALSE) and you obtain the result "b".
Comment 3 gmolleda 2019-04-15 17:36:09 UTC
The solution is:
You can use for search: =VLOOKUP(2,CHOICE({2,1},a1:a4,b1:b4),2,FALSE) and you obtain the result "b".
Comment 4 gmolleda 2019-04-15 17:54:58 UTC
Use ; and not , in Spain and other countries.
In Spain: =BUSCARV(2;ELEGIR({2;1};a1:a4;b1:b4);2;FALSO).
Comment 5 gmolleda 2019-05-02 17:27:09 UTC
I watch it bad, the separator is forever dot (.)
ELEGIR({2.1};a1:a4;b1:b4) in Spain
Comment 6 gmolleda 2019-05-03 21:19:13 UTC
Oh, in matrix, if you work with rows or columns, the separator is different: . and , or ;
Comment 7 gmolleda 2019-11-16 06:38:59 UTC
The best solution:
VLOOKUP -> =INDEX (matrix_results ; MATCH( SearchCriterion ; LookupArray ; type) ; column)
BUSCARH -> =INDEX (matrix_results ; row ; MATCH( SearchCriterion ; LookupArray ; type))
In Spanish:
BUSCARV	 =INDICE (matriz_rtdos ; COINCIDIR( criterio ; vector_buscar ; tipo) ; columna)
BUSCARH	 =INDICE (matriz_rtdos ; fila ; COINCIDIR( criterio ; vector_buscar ; tipo))