Re: [libreoffice-users] vlookup help

Hi James,

James schrieb am 22.06.2024 um 22:12:
> I am having trouble with vlookup.
> I put a test file at:
> https://www.mediafire.com/file/puq9nuu982beicl/vlookup-test.ods/file
>
> The spreadsheet is:
> One    $1    1%
> Two    $2    2%
> Three    $3    3%
> current    $2    2%    #N/A
>
> C4 contains =VLOOKUP(B4,B1:C3,2,0) which works.
> D4 contains =VLOOKUP(C4,A1:C3,1,0) which displays #N/A but I want it to 
> display whatever is in column A of the matching row.
> "Two" in this case.

VLOOKUP cannot access a column that is before the left-most column of 
the lookup-range, and the left-most column of the lookup-range is always 
the column, where the value is searched.

> Whatenever I change B4, D4 should display the matching row of column A/

This can be done with a combination of MATCH with OFFSET or a 
combination of MATCH with INDEX.

For the combination of MATCH with OFFSET you need to add a row of labels 
before your data.

      ColA     colB     ColC
Row1  labelA   labelB   labelC
Row2  One      $1       1%
Row3  Two      $2       2%
Row4  Three    $3       3%
Row5  current  $2       see below

formula in cell C5
=OFFSET(A1; MATCH(B5;B2:B4;0);0)

A1 is the reference cell. To its position OFFSET adds the row difference 
and the column difference. MATCH returns 2. Thus the returned value is 
from cell column A+0, row 1+2, that is from cell A3.


For the combination of MATCH with INDEX you need no label row.
      ColA     colB     ColC
Row1  One      $1       1%
Row2  Two      $2       2%
Row3  Three    $3       3%
Row4  current  $2       see below

formula in cell C4
= INDEX(A1:C3;MATCH(B4;B1:B3;0);1)
Index uses the position row|col whereby the left-top cell in the 
reference area A1:C3 has the position 1|1. Thus position 2|1 is the cell A2.


The upcoming version 24.8 will have a function XLOOKUP. With that 
function the lookup range and the return range are separated.
=XLOOKUP(B4; B1:B3; A1:A3)


(My parameter separator is a semicolon. You need to adapt the formulas 
to use your parameter separator.)

Kind regards,
Regina





--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy