Description: 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()
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)
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".
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".
Use ; and not , in Spain and other countries. In Spain: =BUSCARV(2;ELEGIR({2;1};a1:a4;b1:b4);2;FALSO).
I watch it bad, the separator is forever dot (.) choice({2.1},a1:a4,b1:b4) ELEGIR({2.1};a1:a4;b1:b4) in Spain
Oh, in matrix, if you work with rows or columns, the separator is different: . and , or ;
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))